cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DM_BI Member
Member

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

Accepted Solutions
DM_BI Member
Member

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

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
13 REPLIES 13
Super User
Super User

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

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?

DM_BI Member
Member

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

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

Super User
Super User

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

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)

Highlighted
DM_BI Member
Member

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

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?

Super User
Super User

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

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

DM_BI Member
Member

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

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

Super User
Super User

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

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

DM_BI Member
Member

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

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!

Super User
Super User

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

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