I need to show a Cumulative total in a Matrix table, but it keeps continuing the running total from month to month. I need it to reset at the beginning of each Fiscal month:
Total Shipments TY = calculate([Total Shipments],filter(PBI_AEP_Shipments_B2B_Multi,year(PBI_AEP_Shipments_B2B_Multi[Transaction Date])=[Today Year])) LastSalesDate = LASTNONBLANK( PBI_FSCAPF[PADDATE] , [Total Shipments] ) Cumulative Shipments TY = IF(SELECTEDVALUE (PBI_FSCAPF[PADDATE])> [LastSalesDate], BLANK(), CALCULATE( [Total Shipments TY] , FILTER( ALLSELECTED( PBI_FSCAPF), PBI_FSCAPF[PADDATE] <= MAX( PBI_FSCAPF[PADDATE] ))))
The table is arranged by Calendar Day in Month (CADIM), so what I need is when February is shown, it display what the sales are for only Day 1 of February, and then accumulate from there. It’s picking up January’s sales.