powerbidaxcalculated-columns

I need to get the previous month status of the subkpi

I'll explain a bit on the dataset I have. Each sub-kpi belongs to a KPI (which may have multiple sub-kpis) and each KPI belongs to a department (which may have multiple KPIs). We keep track once every month. Please check a sample of the dataset.

Current dataset

What I want to do is divide the rows between "Show" and "Hide" like you see in the last column. But the rows with "Show" should fulfill 2 conditions:

  1. Current status is "Completed"
  2. Status of the previous month for that same SubKPI under that same KPI and company should be different from "Completed"

Right now, I've only been able to create a new calculated column that achieves the first condition using the formula below. The variable "PreviousMonthStatusValue" does not seem to be getting any value.

PreviousMonthStatus =
VAR CurrentYearMonth = 'Deliverables table'[Date]
VAR PreviousYearMonth = PREVIOUSMONTH('Deliverables table'[Date])
VAR PreviousMonthStatusValue =
  CALCULATE(
        VALUES('Deliverables table'[Deliverable status]),
        PREVIOUSMONTH('Deliverables table'[Date]))
RETURN
      IF(
        'Deliverables table'[Deliverable status] = "Completed" && 
        PreviousMonthStatusValue <> "Completed",
        "Show",
        "Hide"
    )

The desired dataset would be the following one:

enter image description here

Could you please help me? I don't mind following a different procedure like using a measure.

Solution

  • Try a Calculated Column similar to the following.
    Note the use of REMOVEFILTERS() to get the whole table (similar to using ALL(...) )

    PreviousMonthStatus = 
  var thisID = [Department] & "|" & [KPI] & "|" & [Sub KPI]
  var previousNotCompleted = 
    CALCULATE(
      COUNTROWS('Deliverables table'),
      REMOVEFILTERS(),
      PREVIOUSMONTH('Deliverables table'[Date]),
      'Deliverables table'[Department] & "|" & 'Deliverables table'[KPI] & "|" & 'Deliverables table'[Sub KPI] = thisID,
      'Deliverables table'[Deliverable status] = "Completed"
    )
  RETURN IF([Deliverable status] = "Completed" && previousNotCompleted < 1, "Show", "Hide")