Revenue YTD to MTD in calculated column Power Pivot

[Lars Ole Sørensen]

Hi

I am trying to convert YTD data to MTD dato in a calculated column in Power Pivot. I get wrong number in the calculation for just one month, the calculation for period 31.07.2023 and cant find out why. Quantity YTD last month shows 42021 when it should be 1168.

I have attaced the workbook.



Thanks in advance for any help

 

[Debaser]

Your measure is calculating the MAX revenue from prior periods. Since 42021 is higher than 1168, you get 42021. If you just want the prior month, you could use FILTER(Revenue, Revenue[Period]=EOMONTH(ThisPeriod, -1))

 

[Lars Ole Sørensen]

Your measure is calculating the MAX revenue from prior periods. Since 42021 is higher than 1168, you get 42021. If you just want the prior month, you could use FILTER(Revenue, Revenue[Period]=EOMONTH(ThisPeriod, -1))

Yes, of course, this was what I wanted to achieve, thank you very much for the help

 

[GraH - Guido]

Better option is to add a date table, so you can use the build-in time intelligence and use measures, not calculated columns.

Qty YTD:=sum(Revenue[Quantity YTD])

Qty YTD Prev Month:TOTALYTD([Qty YTD],'Calendar'[Date],PREVIOUSMONTH('Calendar'[Date]))

 

[Lars Ole Sørensen]

Better option is to add a date table, so you can use the build-in time intelligence and use measures, not calculated columns.

Qty YTD:=sum(Revenue[Quantity YTD])

Qty YTD Prev Month:TOTALYTD([Qty YTD],'Calendar'[Date],PREVIOUSMONTH('Calendar'[Date]))

Ok, thank you very much