cancel
Showing results for
Did you mean:
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,

1 ACCEPTED SOLUTION

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

Best regards,

DM
13 REPLIES 13
Highlighted
Super Contributor

## 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?

Proud to be a Datanaut!

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 Contributor

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

Proud to be a Datanaut!

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 Contributor

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

Proud to be a Datanaut!

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 Contributor

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

Proud to be a Datanaut!

Member

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

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.

Super Contributor

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

as per this video

Proud to be a Datanaut!

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 3,588 guests
Recent signins: