• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Revenue YTD to MTD in calculated column Power Pivot

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.

1692263085011.png

Thanks in advance for any help
 

Attachments

  • 2023.08.17 Revenue YTD to MTD in calculated column Power Pivot.xlsx
    244.7 KB · Views: 7
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))
 
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 :)
 
Better option is to add a date table, so you can use the build-in time intelligence and use measures, not calculated columns.

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

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