Compute MTD based on YTD data - Unable to handle 1st month exception

[Stiphn]

Hi all,

I've been searching for a solution to my problem without success, and would gladly receive insights!

I have a single table with sales by product, combined from multiple reporting, all in YTD data. I have 2021 till November, 2020 over 12 months, and the monthly Budget, for multiple entities (so 10+12+12 entries for one entity and one product

The way I get my data is a huge table with a reporting name (R2021.XX, R2020.XX, B2021.XX)

I am able to Transform to have a Reporting type, the year and the month, and then create a DATE column with the YEAR and MONTH inputs -> therefore I have several time the same date

I am trying to compute the Monthly performance, to do so, I have to substract the YTDSales from previous months. I tried to adapt the Quick Measure already built in PowerBI.

MTDAmount = 
IF(
        ISFILTERED('P&L'[Date]),
	ERROR("ERROR"),
        VAR __PREV_MONTH =
                CALCULATE(
		       SUM('P&L'[YTDAmountLocal]),
		       DATEADD('P&L'[Date].[Date], -1, MONTH)
		)
	RETURN
		SUM('P&L'[YTDAmountLocal]) - __PREV_MONTH
)

However, while it works well for the months starting February, as you can imagine, the January Monthly performance is computed vs December YTD N-1, and therefore is wrong.

I thought about testing the returned Month and add another condition to return 0 for the PREV_MONTH is MONTH('P&L[Date]) was not strictly above 1. But the formula does not seem to work because of multiple dates existing with the same value in my data file.

How would you recommend to deal with that? Many thanks!

[lbendlin]

"while it works well for the months starting February, as you can imagine, the January Monthly performance is computed vs December YTD N-1, and therefore is wrong."

Please explain why this is wrong.

Usually you want to have a calendar table with contiguous dates (even though you only map your facts to one day each month) and then do all the time intelligence against that calendar table (NOT the fact tables).

[Stiphn]

Hi, Thanks for this, I am exploring the Calendar table option.

In our Financials, the data is stored as YTD value, so to compute the January performance, I can use the data as it is in the table. to give an example, IF my YTD Sales as at December N are 100, and at January N+1 10, this gives me a -90 MTD sales