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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DM_BI
Helper III
Helper III

Cumulative value using slicer and not summing values of the same month of different years

Hello,

 

I have trouble showing the cumulative facturation of each month and being able to use my slicer. I've tried several solutions I found on this forum but I can't get the right result in the end. My main problem is that when I show the data in a column chart with the months on the horizontal axe, DAX sums all the values of a same month, regardless to the year (for example the column chart of november sums nov17 and nov18), I think it's because I use ALL(DimDate[Date]).

 

So my question is : is it possible to show a chart with cumulative values by month, being able to use the date slicers (with keeping the cumulation) and explaining to DAX how to differenciate the years?

 

Thank you,

Captura cumulative value.PNG

1 ACCEPTED SOLUTION

I think I've found the solution. We have to use ALLSELECTED instead of ALL because ALLSELECTED retains the filters. For me it works perfectly with this formula : 

Cumulative Total = CALCULATE(SUM('MAT 18+19'[FACTURACIÓN NETA]);FILTER(ALLSELECTED(DimDate);'DimDate'[Date] <= MAX('DimDate'[Date]))
 
I hope it can help other people. Smiley Happy
 
Best regards,
 
DM

View solution in original post

13 REPLIES 13
Stachu
Community Champion
Community Champion

does the 2017/2018 slicer filter 2 whole years? it seems there would be no problem if it was only mapped for Nov 2017 - Oct 2018, is that an option?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hello,

 

Actually the slicer 2017/2018 goes from November 1st 2017 to October 31 2018 (academics years).

You're right and I don't understand why the result of November is so high, it should show only November 2017...

No sorry it goes from November 6th 2017 to November 2nd 2018 (the academic calendar is like that and I can't change it), maybe the problem comes from that?

Stachu
Community Champion
Community Champion

if that's the case you actually have to show it twice not once.
use double fork to drill down rather than regulat arrow, then it should be fine



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

You mean using the matrice table ? Yes it differenciates the years but I also need it correctly in the graph. Isn't possible?

Stachu
Community Champion
Community Champion

no, I mean in the graph
you basically need to show Year & Month rather than just month, so it would start with 2017 Nov and end with 2018 Nov
you can achieve that by adding year to the graph and drilling down to month with double fork



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Oh ok you mean like this : 

Captura cumulative value 2.PNG

Yes I can see it now. But I realize my problem is that I haven't indicated the exact start and end date of each month. For example, I have to tell that october 2018 actually begins on october 8th 2018 and ends on november 2nd 2018. That way I will have the right value on my graph (and won't have to use double fork). I'll try to create a new column in DimDate with 24 (2x12 months) IF formulas. Will let you know.

 

Thank you for your help!

Stachu
Community Champion
Community Champion

no, I mean Year and Month on the X axis, not Year in the legend, you may want to disable the Concatenate labels in X axis
Capture.PNG
as per this video
https://www.youtube.com/watch?v=9DeAKM4SNJM



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Stachu
Community Champion
Community Champion

can you share sample anonymised data and the syntax of the measure you're using?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hello,

 

Here is a sample where I reproduced whaat I'm doing. What is weird is that in the sample I can't get the cumulative total when it's the same formula... The idea is create a measure (named cumulative total) that gives the cumulative facturation by month, and when I filter by year, the graph shouldn't sum the facturation of the last  year but start a new calculation of cumulative total. 

Tell me if I'm not clear.

 

Thank you very much for your time.

 

https://gplaneta-my.sharepoint.com/:u:/g/personal/udm06a_psoplaneta_com/EUk1XfqMUX5BmVRUi3ZeDksBVraH...

I think I've found the solution. We have to use ALLSELECTED instead of ALL because ALLSELECTED retains the filters. For me it works perfectly with this formula : 

Cumulative Total = CALCULATE(SUM('MAT 18+19'[FACTURACIÓN NETA]);FILTER(ALLSELECTED(DimDate);'DimDate'[Date] <= MAX('DimDate'[Date]))
 
I hope it can help other people. Smiley Happy
 
Best regards,
 
DM

Oh ok I didn't know it was possible, thank you. Actually I don't need to do it because we are used to building reports in terms of academic years. 

I put the right start and end dates to my months so now my graph is now ok !!

Captura cumulative value 3.PNG

 

But I have a second problem : when I use the slicer of academic years, I have a wrong result for "2018/2019"... I have the total of the year 2017/2018 PLUS the value of november 2018. But what I want when I filter by 2018/2019 is to come back to zero (because it's a new year) and not to sum with what have been done last year.

 

Captura cumulative value 4.PNG

Do you where the problem can come from? Is is possible?

 

Thank you very much,

Stachu
Community Champion
Community Champion

are you sure it's mapped properly? in the table on the bottom right I see Nov 2 times, once for 2017 and once for 2018 (two top rows)



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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