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

[Anonymous]

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

Entity	Month	Date	Entity Reported Key	Month Sort	Entity-Month Key
Entity A	September	01-Sep-22	Entity A-September	1	Entity A-September
Entity B	September	01-Sep-22	Entity B-September	1	Entity B-September
Entity C	September	01-Sep-22	Entity C-September	1	Entity C-September
Entity A	October	01-Oct-22	Entity A-October	2	Entity A-October
Entity B	October	01-Oct-22	Entity B-October	2	Not Reported
Entity C	October	01-Oct-22	Entity C-October	2	Not Reported

FactTable

Entity-Month Key	Attribute	Value	Attribute (Staffing Type)	Attribute (New/Proj)
Entity A-September	#ECE_NewFTE	0	Early Childhood Educators	New
Entity A-September	#Principals_NewFTE	0	Principals	New
Entity A-September	#VP_NewFTE	0	Vice-Principals	New
Entity A-September	#OtherAdmin_NewFTE	0	Other School Admin	New
Entity A-September	#Cust_NewFTE	0	Custodians	New
Entity A-September	#OtherEdWork_NewFTE	0	Other Educational Workers	New
Entity B-September	#Teach_NewFTE	0	Teachers, Excluding OT	New
Entity B-September	#ECE_NewFTE	0	Early Childhood Educators	New
Entity B-September	#EA_NewFTE	0	Educational Assistants	New
Entity B-September	#Principals_NewFTE	0	Principals	New
Entity B-September	#VP_NewFTE	50	Vice-Principals	New
Entity B-September	#OtherAdmin_NewFTE	0	Other School Admin	New
Entity B-September	#Cust_NewFTE	0	Custodians	New
Entity B-September	#OtherEdWork_NewFTE	0	Other Educational Workers	New
Entity C-September	#Teach_NewFTE	0	Teachers, Excluding OT	New
Entity C-September	#ECE_NewFTE	0	Early Childhood Educators	New
Entity C-September	#EA_NewFTE	0	Educational Assistants	New
Entity C-September	#Principals_NewFTE	0	Principals	New
Entity C-September	#VP_NewFTE	0	Vice-Principals	New
Entity C-September	#OtherAdmin_NewFTE	0	Other School Admin	New
Entity C-September	#Cust_NewFTE	0	Custodians	New
Entity C-September	#OtherEdWork_NewFTE	100	Other Educational Workers	New
Entity A-September	#Teach_NewFTE	10	Teachers, Excluding OT	New
Entity A-September	#EA_NewFTE	20	Educational Assistants	New

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

[Anonymous]

Hi @Anonymous ,

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

Best Regards,

Jay

[Anonymous]

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.