Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
I am trying to calculate running total on active projects only. For better understanding please find below example of 2 tables:
1. DIM_Date table (standard calculated table)
2. FACT_Projects
Table FACT_Projects contains following columns:
Project ID | Start date | End date |
A | 1.11.2022 | 25.1.2023 |
B | 17.11.2022 | 8.3.2023 |
C | 1.12.2022 | 31.12.2022 |
D | 1.1.2022 | |
E | 15.1.2022 | text( e.g. unlimited) |
I have managed to create the measure that require duplication of FACT_Projects table and then creating relationships with Date table as per below:
DIM_Date and FACT_Project with Start date
DIM_Date and FACT_Project COPY with End date
Then using standard formula for cumulative/running total, I have counted separately nr. of created projects and nr. of closed projects. And then, the difference between those are indicating the cumulative nr. of active projects.
Cumulative total (Start date) =
CALCULATE (
COUNT ( 'FACT_Projects'[ID] ),
FILTER (
ALL ( 'FACT_Projects' ),
'FACT_Projects'[Start date] <= MAX ( 'FACT_Projects'[Start date] )
)
)
Cumulative total (End date) =
CALCULATE (
COUNT ( 'FACT_Projects COPY'[ID] ),
FILTER (
ALL ( 'FACT_Projects COPY' ),
'FACT_Projects COPY'[End date] <= MAX ( 'FACT_Projects COPY'[End date] )
)
)
However, my data model consists of many tables and I would like to avoid any duplication of tables where possible. Therefore I am looking for solution with only one measure. Also, I didn't manage is to handle text values as in column End date, values can be either date, blank or text "unlimited".
Thank you for any help!
IvanS
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |