Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
BeginnerAnalyst
Frequent Visitor

Total year to date not working

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:
BeginnerAnalyst_0-1682354210278.png

 

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

4 REPLIES 4
Greg_Deckler
Super User
Super User

@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:

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors