Desktop

[DM_BI]

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,

[DM_BI · 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. 

Best regards,

DM

[Stachu]

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]

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

[Stachu]

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)

[DM_BI]

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]

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]

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

[Stachu]

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]

Oh ok you mean like this : 

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]

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

as per this video
https://www.youtube.com/watch?v=9DeAKM4SNJM