Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IvanS
Helper V
Helper V

Cumulative/Running total on active projects

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 IDStart dateEnd date
A1.11.202225.1.2023
B17.11.20228.3.2023
C1.12.202231.12.2022
D1.1.2022 
E15.1.2022text( 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

 

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.