TOTALYTD DAX function doesn't work

[thimremy]

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).

 

Does someone have an idea?

Thanks a lot!

[MFelix · Accepted solution]

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

[thimremy]

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]

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

[thimremy]

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