Reverse YTD with changing year-end

[Anonymous]

Hi folks,

I'm struggling with converting YTD financial data to periodic (MTD) that comes from different year-end regimes.

Imported	Imported	Imported	Imported	DAX
Account	Date	YTD Value	DataType	MTD Value
AB	Dec 20	160	CY	160
AB	Jan 21	50	CY	50
AB	Feb 21	90	CY	40
AB	Mar 21	110	CY	20
AB	Apr 21	50	FY	50
Pivot Total				320

In the table above, CY stands for Calendar Year while Fiscal Year now ends in March. Is it possible using a DAX measure to convert YTD to MTD resulting in the above MTD values including correct Pivot Totals?

Best regards,

Chris

[lbendlin]

MTD for a completed month is the same as "value for that month".  Use that field for your fiscal year time intelligence function.

[Anonymous]

Hi, my solution yielded the following dax with a little help from power query M:

Value MTD CY:=CALCULATE([YTD Value];[DataType]="CY") -

CALCULATE([YTD Value];[DataType]="CY"; DATEADD([Date];-1;MONTH);MONTH[Date]<12;[SubtractYTD])

Value MTD FY:=CALCULATE([YTD Value];[DataType]="FY") -

CALCULATE([YTD Value];[DataType]="FY"; DATEADD([Date];-1;MONTH);MONTH[Date]<>3;[SubtractYTD])

Value MTD:= Value MTD CY + Value MTD FY

SubtractYTD compares Date with MaxDate by DateType in calculated column due to underlying data characteristics.