Need CUMULATIVE measure to reset by Fiscal Month

[Roseventura]

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.

My formulas:

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.

[Anonymous · Accepted solution]

You need the counterpart of the MTD calculation but based on a fiscal calendar. Here's one way to do it:

// Please adjust the names accordingly.
// Bear in mind that this will only return
// non-BLANK if there is only one
// FiscalMonthNumber visible in the current
// context. This is due to the SELECTEDVALUE
// function that will return BLANK if there
// is more than 1 fiscal month visible.

Cumulative MTD :=
// Dates is your calendar.
var __minDateVisible = MIN ( 'Dates'[DateKey] )
var __maxDateVisible = MAX ( 'Dates'[DateKey] )
// FiscalMonthNumber is an integer that numbers
// fiscal months from 1 up to the last one in
// your Dates, consecutively.
var __currentFiscalMonth = SELECTEDVALUE ( 'Dates'[FiscalMonthNumber] )
// Transactions is your fact table.
var __lastTransactionDate =
	CALCULATE (
		MAX ( Transactions[DateKey] ),
		ALL ( Transactions ) 
	)
// Gets all the dates in the current fiscal
// month from the first day to the last
// visible in the current context.
var __filter =
	filter (
		all ( Dates ),
       	        'Dates'[Date] <= __maxDateVisible
        	&&
        	'Dates'[FiscalMonthNumber] = __currentFiscalMonth
	)
var __cumulative =
    CALCULATE (
        [Your Measure],
        __filter
    )
return
// This IF is here to make sure
// that the value is not calculated
// in periods that start after the
// very last transaction date.
	IF (
		__minDateVisible <= __lastTransactionDate,
		__cumulative
	)

Best

Darek

[Roseventura · Accepted solution]

I will try this solution and get back to you.  (Sorry...was on vacation.)

Rose

[Anonymous · Accepted solution]

You need the counterpart of the MTD calculation but based on a fiscal calendar. Here's one way to do it:

// Please adjust the names accordingly.
// Bear in mind that this will only return
// non-BLANK if there is only one
// FiscalMonthNumber visible in the current
// context. This is due to the SELECTEDVALUE
// function that will return BLANK if there
// is more than 1 fiscal month visible.

Cumulative MTD :=
// Dates is your calendar.
var __minDateVisible = MIN ( 'Dates'[DateKey] )
var __maxDateVisible = MAX ( 'Dates'[DateKey] )
// FiscalMonthNumber is an integer that numbers
// fiscal months from 1 up to the last one in
// your Dates, consecutively.
var __currentFiscalMonth = SELECTEDVALUE ( 'Dates'[FiscalMonthNumber] )
// Transactions is your fact table.
var __lastTransactionDate =
	CALCULATE (
		MAX ( Transactions[DateKey] ),
		ALL ( Transactions ) 
	)
// Gets all the dates in the current fiscal
// month from the first day to the last
// visible in the current context.
var __filter =
	filter (
		all ( Dates ),
       	        'Dates'[Date] <= __maxDateVisible
        	&&
        	'Dates'[FiscalMonthNumber] = __currentFiscalMonth
	)
var __cumulative =
    CALCULATE (
        [Your Measure],
        __filter
    )
return
// This IF is here to make sure
// that the value is not calculated
// in periods that start after the
// very last transaction date.
	IF (
		__minDateVisible <= __lastTransactionDate,
		__cumulative
	)

Best

Darek

[Roseventura · Accepted solution]

I will try this solution and get back to you.  (Sorry...was on vacation.)

Rose

[Roseventura · Accepted solution]

This solution worked perfected for me.

Thank you!!