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
