Calculating MTD value from YTD values

[ErikBe]

Hi,

I have a data table with only YTD sales value, by month, and would like to calculate the MTD values. Being a PowerBi beginner I cannot figure out how to get this done and when searching the forum for clues, all I found was answers on how to solve the opposite problem, i.e.  calculating MTD/QTD/YTD etc from daily or monthly figures.

Below is a sample table of how the data looks and fyi, I managed to create a date_table which I guess comes in handy here.

Month (YYYY-MM)	YTD Sales	MTD Sales
2019-12	1000	?
2020-01	100	?
2020-02	300	?
2020-03	500	?
2020-04	900	?

I appreciate any support!

Stay safe,

Erik 

[DataInsights · Accepted solution]

@ErikBe,

Try this calculated column. I recommend a calculated column instead of a measure, because having MTD in a column will allow you to perform various time intelligence calculations.

MTD Sales =
VAR vDate = YTDtoMTD[Month Ending]
VAR vPrevDate =
    CALCULATE (
        MAX ( YTDtoMTD[Month Ending] ),
        ALL ( YTDtoMTD ),
        YTDtoMTD[Month Ending] < vDate
    )
VAR vPrevSales =
    CALCULATE (
        SUM ( YTDtoMTD[YTD Sales] ),
        ALL ( YTDtoMTD ),
        YTDtoMTD[Month Ending] = vPrevDate
    )
VAR vYTD = YTDtoMTD[YTD Sales]
VAR vMTD = vYTD - vPrevSales
VAR vResult =
    IF ( MONTH ( vDate ) = 1, vYTD, vMTD )
RETURN
    vResult

[ErikBe]

Thank you very much @DataInsights