Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

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.

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.

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

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

     

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

     

    Best Regards,

    Jay

    • Anonymous's avatar
      Anonymous
      Not applicable

      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.