cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gwhiz Regular Visitor
Regular Visitor

need help modifying this cumulative dax formula

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:

 

dax1.pngdax2.png

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

dax3.png

3 REPLIES 3
Super User
Super User

Re: need help modifying this cumulative dax formula

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

 

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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Gwhiz Regular Visitor
Regular Visitor

Re: need help modifying this cumulative dax formula

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

Gwhiz Regular Visitor
Regular Visitor

Re: need help modifying this cumulative dax formula

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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 256 members 2,690 guests
Please welcome our newest community members: