12-03-2018 08:26 AM - edited 12-03-2018 08:32 AM
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?
Solved! Go to Solution.
12-10-2018 01:24 AM
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 :
12-03-2018 08:55 AM
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?
12-03-2018 08:59 AM
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...
12-03-2018 09:01 AM
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)
12-03-2018 09:01 AM
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?
12-03-2018 09:04 AM
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
12-03-2018 09:15 AM
You mean using the matrice table ? Yes it differenciates the years but I also need it correctly in the graph. Isn't possible?
12-04-2018 02:00 AM
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
12-04-2018 02:17 AM
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!
12-04-2018 02:32 AM - edited 12-04-2018 02:33 AM
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