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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
thimremy
Frequent Visitor

TOTALYTD DAX function doesn't work

Hello,

I'm following Power BI tuto and good news, I'm already stuck at the TOTALYTD DAX function.

 

Here is an extract of the test table (Excel) I created (and I generated around 700 lines):

Sales

Date

MonthNumber

Year

4

01/01/2016

1

2016

6

02/01/2016

1

2016

8

03/01/2016

1

2016

38

04/01/2016

1

2016

54

05/01/2016

1

2016

2

06/01/2016

1

2016

5

07/01/2016

1

2016

23

08/01/2016

1

2016

24

09/01/2016

1

2016

 

I'd like to have a cumulative view of the sales by month for each year so I created this variable:

 

YTD = TOTALYTD(SUM(Sheet1[Sales]);Sheet1[Date])

 

When I create a matrix with MonthNumbers as rows, Years as columns and YTD as values (right matrix), I get the same as when I create a matrix with MonthNumbers as rows, Years as columns and Sales as values (right matrix).

 

 Capture.PNG

 

Does someone have an idea?

 

Thanks a lot!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @ thimremy,

Since you are using a date column in the same table as the sales data when you use the month column the calculation will be made month on month and not based on the YTD measure you nwed to add the date column to your table and select the hierarchy and theb the month that qay you will have the YTD calculation.

One good practice for this kind of calculation is to create a calendar table with non repetive dates and with month, year, quarter,... columns and then create a relationship between the calendar and the Sales date then use the columns in the calendar to make the axis/main columns in your visuals that way the measures will work ad expectef.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

Hi @ thimremy,

Since you are using a date column in the same table as the sales data when you use the month column the calculation will be made month on month and not based on the YTD measure you nwed to add the date column to your table and select the hierarchy and theb the month that qay you will have the YTD calculation.

One good practice for this kind of calculation is to create a calendar table with non repetive dates and with month, year, quarter,... columns and then create a relationship between the calendar and the Sales date then use the columns in the calendar to make the axis/main columns in your visuals that way the measures will work ad expectef.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix,

 

Thank you for your solution! 

 

By creating a seperate calendar table and linking the 2 tables, my measures perfectly works.

 

Regards,

thimremy

Mariam1991
Helper II
Helper II

Hi @thimremy

I dont get your point , u need to calculate cumulative sum of sales by month ?

if u neeed to calculate sum of sale group by month 

measure= calculate(sum(sheet1[sales]); DATESMTD(Sheet1[Date])

Let me know your need exactly

Hi @Mariam1991,

 

Yes, I need to calculate a cumulative sum of sales througout the months (For example: for February 2017, I need to sum sales of January & February 2017).

As you said, to calculate sum of sales group by month, the following measure you offered perfectly works:

 

measure= calculate(sum(sheet1[sales]); DATESMTD(Sheet1[Date])

 

And to calculate a cumulative sum of sales througout the months, I tried @MFelix's solution to create a separate calendar table and I used my initial measure (YTD = TOTALYTD(SUM(Sales[Sales]);Date[Date])) and it perfectly worked.

 

Thank you for having answered so quickly!

 

Regards,

 

thimremy

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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