topic Re: need help modifying this cumulative dax formula in DAX Commands and Tips

need help modifying this cumulative dax formula

Gwhiz — Thu, 23 May 2019 17:20:45 GMT

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 happens when I try the formula above:

I changed the formula around so now it resets every month but it gives me a descending cumulative sum?

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

Re: need help modifying this cumulative dax formula

parry2k — Sat, 25 May 2019 13:18:44 GMT

Gwhiz you should use less than equal to instead of greater than equal to

Opportunity[CreatedDate].[date]<=MAX(Opportunity[CreatedDate].[date])

Re: need help modifying this cumulative dax formula

Gwhiz — Tue, 28 May 2019 15:54:25 GMT

parry2k I used that in the first formula in my post and you can see that the sum does not reset every month that way.

Re: need help modifying this cumulative dax formula

Gwhiz — Tue, 28 May 2019 18:33:24 GMT

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

Some of the dates got cut off but 2015 is a good year to look at.