Need help modifying dax formula for cumulative sumx

[Gwhiz]

I am having trouble getting this formula to reset every month as it doesn't go to 0 but when I have it reset each year it works fine:

Business cumulative = 
SUMX(
    FILTER(
        ALLSELECTED(Opportunity[CreatedDate].[date]),
        Opportunity[CreatedDate].[date]<=MAX(Opportunity[CreatedDate].[date])
        && MONTH(Opportunity[CreatedDate].[date])= month(max(Opportunity[CreatedDate].[date]))
        )
        ,[business days]
    )

If I literally just change out "month" for "year" in this formula it works for a yearly cumulative but it never works on the month level. Here is what I mean:

I changed the formula around and now it resets on each month but it is a descending cumulative? How do I reverse that so that it is ascending?

Business cumulative = 
SUMX(
    FILTER(
        ALLSELECTED(Opportunity[CreatedDate].[date]),
        Opportunity[CreatedDate].[date]>=MAX(Opportunity[CreatedDate].[date])
        && month(Opportunity[CreatedDate].[date])= month(max(Opportunity[CreatedDate].[date]))
        )
        ,[business days]
    )

[v-yuta-msft]

@Gwhiz ,

In general, you may create an index column and create a calculate column using DAX like pattern below:

Business cumulative =
SUMX (
FILTER (
ALLSELECTED ( Opportunity[CreatedDate].[date] ),
Opportunity[CreatedDate].[date] >= MAX ( Opportunity[CreatedDate].[date] )
&& MONTH ( Opportunity[CreatedDate].[date] )
= MONTH ( MAX ( Opportunity[CreatedDate].[date] ) )
&& Opportunity[Index] <= EARLIER ( Opportunity[Index] )
),
[business days]
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gwhiz]

@v-yuta-msft   Any other way than creating an index column?? I don't think that would be the best way to go about it, if you see my last formula it is exactly what I want but I need it changed from descending to ascending.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Gwhiz]

@Ashish_Mathur Here is a replica of the file: https://www.dropbox.com/s/ay6uqsw4e1ocdpe/cumulative%20business%20days.pbix?dl=0

Some of the dates got cut off so i would only look at the year 2015. Seems like the reverse formula only works for some months which baffles me.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution PBI file from here.

Hope this helps.

[Anonymous]

Thanks! Your solution actually helps me resolve a related challenge I had. I was trying to get a cumulative sum based on a measure and your DATEBETWEEN formula combined with SUMX basically resolved it.

[Gwhiz]

@Ashish_Mathur 

One question though, can you not use date hierarchies in this scenario or was this just your preference? would the date order table be necessary in a bigger dataset/real scenario?

[Ashish_Mathur]

Hi,

I never prefer to use Date hierarchies.  With a seperate Calendar Table, one can easily use Date and Time Intelligence functions.  Yes, the Date order table would be necessary.