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
AccrualJoke
Helper I
Helper I

Conditional Cumulative (Running) Total Measure - Do Not Calculate or Display If...

Problem: Trying to create a cumulative / running total measure that will only calculate if data for the specified or selected time frame is available. Measure is expected to change based on slicer selections: (1) Survey Month (2) Entity

Current State:
For the month of September (month 1), visual and calculation are displaying counts as intended.

AccrualJoke_0-1667498066235.png

For October (Month 2), no data has been submitted yet. So monthly counts at 0 are correct. But cumulative amounts are intended to be 0 as well, as cumulative amounts should only show if data is reported for that given month.

AccrualJoke_1-1667498222494.png

I've attempted a measure like this. Upon drilldown to the entity level, it works as intended. E.g. It'll show only the entities that reported and their monthly and cumulative amounts. But at this "staff type" level, I just can't get it to work:

 

Monthly_Hire =
CALCULATE (
    SUM ( 'FactTable'[Value] ),
    KEEPFILTERS ( 'FactTable'[Attribute (New/Proj)] = "New" )
)

Cumulative Hires =
IF (
    SELECTEDVALUE ( 'EntityTable'[ReportStatus] ) = "Not Reported",
    BLANK (),
    CALCULATE (
        [Monthly_Hire],
        ALLEXCEPT ( 'EntityTable', 'EntityTable'[Entity], 'EntityTable'[Entity-Month Key] ),
        'EntityTable'[Month Sort] <= MAX ( 'EntityTable'[Month Sort] )
    )
)

 

Sample Data

Could not include full sample data due to character limit, but this should be sufficient.

Entity Table

EntityMonthDateEntity Reported KeyMonth SortEntity-Month Key
Entity ASeptember01-Sep-22Entity A-September1Entity A-September
Entity BSeptember01-Sep-22Entity B-September1Entity B-September
Entity CSeptember01-Sep-22Entity C-September1Entity C-September
Entity AOctober01-Oct-22Entity A-October2Entity A-October
Entity BOctober01-Oct-22Entity B-October2Not Reported
Entity COctober01-Oct-22Entity C-October2Not Reported

 

FactTable

Entity-Month KeyAttributeValueAttribute (Staffing Type)Attribute (New/Proj)
Entity A-September#ECE_NewFTE0Early Childhood EducatorsNew
Entity A-September#Principals_NewFTE0PrincipalsNew
Entity A-September#VP_NewFTE0Vice-PrincipalsNew
Entity A-September#OtherAdmin_NewFTE0Other School AdminNew
Entity A-September#Cust_NewFTE0CustodiansNew
Entity A-September#OtherEdWork_NewFTE0Other Educational WorkersNew
Entity B-September#Teach_NewFTE0Teachers, Excluding OTNew
Entity B-September#ECE_NewFTE0Early Childhood EducatorsNew
Entity B-September#EA_NewFTE0Educational AssistantsNew
Entity B-September#Principals_NewFTE0PrincipalsNew
Entity B-September#VP_NewFTE50Vice-PrincipalsNew
Entity B-September#OtherAdmin_NewFTE0Other School AdminNew
Entity B-September#Cust_NewFTE0CustodiansNew
Entity B-September#OtherEdWork_NewFTE0Other Educational WorkersNew
Entity C-September#Teach_NewFTE0Teachers, Excluding OTNew
Entity C-September#ECE_NewFTE0Early Childhood EducatorsNew
Entity C-September#EA_NewFTE0Educational AssistantsNew
Entity C-September#Principals_NewFTE0PrincipalsNew
Entity C-September#VP_NewFTE0Vice-PrincipalsNew
Entity C-September#OtherAdmin_NewFTE0Other School AdminNew
Entity C-September#Cust_NewFTE0CustodiansNew
Entity C-September#OtherEdWork_NewFTE100Other Educational WorkersNew
Entity A-September#Teach_NewFTE10Teachers, Excluding OTNew
Entity A-September#EA_NewFTE20Educational AssistantsNew

 

Expected Result

Probably not the best illustration of the expected result. But as you can see, only Entity A reported in October. Therefore, only their cumulative counts appear.

AccrualJoke_0-1667831445935.png

Sample data included above.

Connected as 1:* (Board-Reported Key to Board-Month Key)

 

Appreciate any help I can get on this!

Thank you

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @AccrualJoke ,

 

Please share some sample data and expected result so that we could test the formula.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Thank you for replying! I've edited the post to include sample data + expected result.

After discussing with the client, they decided to drop this change request. But it would be nice to know if something like this is even possible.

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.