YTD-MTD does not work for Column Total

Anything related to PowerPivot and DAX Formuale
Franz
Posts: 4
Joined: Mon Feb 25, 2019 5:31 am

YTD-MTD does not work for Column Total

Postby Franz » Fri Apr 12, 2019 10:07 pm

Dear all

I am (still) trying to convert a set of YTD numbers to MTD numbers.
Thanks to a tip from Matt I managed to get some result ... but there is a but.

As a start:
- I don't have a proper calendar, but a substitute. I am not using an inbuilt time function but a Calculate/Filter formula.
- I have the YTD numbers, I am calculating the YTD number of the prior month and then take the difference as MTD number.

The problem: the formula works on a row level but not on the total level.
YTD-MTD.png
YTD-MTD.png (12.3 KiB) Viewed 70 times


I guess it might be a very simple solution ... I just don't get it. Any help would be highly appreciated.
I also attach the workbook - just in case :-)

Best regards
Franz
Attachments
Converting YTD to MTD_v3.xlsx
(307.99 KiB) Downloaded 2 times

MattAllington
Posts: 1036
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: YTD-MTD does not work for Column Total

Postby MattAllington » Sun Apr 14, 2019 9:42 am

How about this

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

=
SUMX (
    VALUES ( Calendar[Period] ),
    IF (
        [Total Amount YTD] > 0,
        [Total Amount YTD] - [Total Amount Prior Month YTD]
    )
)


Read my article about SUM vs SUMX
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests

cron