cancel
Showing results 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.

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:

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"?

4 REPLIES 4
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:

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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.

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors