topic Re: Total year to date not working in DAX Commands and Tips

Total year to date not working

BeginnerAnalyst — Mon, 24 Apr 2023 17:02:06 GMT

Hello everyone)

I have pbi report with sales bonus calculation.

All data is in the table called "Revenue".

I need to create a measure that will sum values total to date for every manager from

"Quarter payment" column / measure.

My current measure for YTD values is:

YTD payment = TOTALYTD([Quarter payment], 'Calendar'[Date])

But it is not working:

The expected result for selected manager should be:

Q1 2023 - 2 693.93

Q2 2023 - 8 887.13

Q3 2023 - 16 320.03

Q4 2023 - 25 240.33

Can somebody help me in creating correct measure "YTD payment"?

Thanks in advance.

Here is pbix file: https://drive.google.com/file/d/18mC4bQjEwjldfHZ2XxxlXeQVxu3au5Za/view?usp=sharing

Re: Total year to date not working

Greg_Deckler — Mon, 24 Apr 2023 17:01:58 GMT

BeginnerAnalyst See if this helps: Better Year to Date Total - Microsoft Power BI Community

And this video that presents better alternatives for YTD, QTD, MTD and WTD:

Re: Total year to date not working

BeginnerAnalyst — Mon, 24 Apr 2023 17:06:03 GMT

thx, but this is not working solution for my case(

Re: Total year to date not working

Greg_Deckler — Mon, 24 Apr 2023 19:57:44 GMT

BeginnerAnalyst Here's the start of it, you'll have to add in the rest of your logic.

Measure = VAR __MaxDate = MAX('Calendar'[Date]) VAR __Manager = MAX('Revenue'[Manager Name]) VAR __YearQuarter = YEAR(__MaxDate) * 100 + QUARTER(__MaxDate) VAR __PrevYearQuarter = IF(QUARTER(__MaxDate) = 1, ( YEAR(__MaxDate) - 1 ) * 100 + 4, YEAR(__MaxDate) * 100 + QUARTER(__MaxDate) - 1) VAR __Table = SUMMARIZE( FILTER(ALLSELECTED('Revenue'), [Type] = "Actual"), [date], "__YQ", YEAR([date]) * 100 + QUARTER([date]), "__Payment", SUM('Revenue'[amount usd]) ) VAR __Quarter = SUMX(FILTER(__Table, [__YQ] = __YearQuarter), [__Payment]) VAR __PQ = SUMX(FILTER(__Table, [__YQ] = __PrevYearQuarter), [__Payment]) VAR __Result = DIVIDE( __Quarter - __PQ, __PQ) RETURN __Result

Re: Total year to date not working

BeginnerAnalyst — Tue, 25 Apr 2023 08:52:21 GMT

thank you for reply) but the idea is not to sum Revenue'[amount usd]. I need to sum quarter payments cumulative to date for each year.