Hi all,
I am trying to create a historical trendline showing the past status of a KPI in a table but struggle to do so
Below is the example table used
Table_1
| Order Number | Status | Date Approved | Date Closed | Condition A | Condition B |
| 1 | Open | 1-Nov-17 | | No | No |
| 2 | Closed | 2-Feb-18 | 1-Jan-19 | No | No |
| 3 | Open | 3-Mar-18 | | Yes | No |
| 4 | Open | 4-Apr-19 | | No | No |
| 5 | Closed | 10-Apr-19 | 2-Feb-20 | No | No |
Using the table as an example, let's define the open record KPI for that reporting month as KPI-01
I am able to calculate the KPI record for the current month by creating this measure:
KPI-01 =
Calculate ( DistinctCountNoblank[OrderNumber], Filter(Table_1, Table_1[Condition A]= "No" && Table_1[Condition B]= "No], Table_1[Status] = "Open"]
Note : Condition A, B need to be "No" and Status need to be Open in order to qualify as a Count in KPI-01
However, I want to create a historical line chart where it shows the no. of KPI-01 based on the slicer date and not affected by the record status e.g.
- If i sliced the date to May 18, it should show the historical KPI-01 value of 2 records
- If i sliced the date to Jan 20, it should show KPI-01 value as 3 records.
- If i sliced the date till today, it should show KPI-01 value as 2 records
I have written the following running total dax only to realise that it is affected by the record status.
Total KPI-01 =
CALCULATE([KPI-01] ,
FILTER(
All(Table_1), Table_1[Date Approved ] <= MAX(Table_1[Date Approve])))
How can the running total DAX be written to reflect the historical record trend without being affected by the record status ?
Appreciate your help in this issue. Thanks !
