Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Hi @AccrualJoke ,
Please share some sample data and expected result so that we could test the formula.
Best Regards,
Jay
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |