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

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

3 REPLIES 3
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])`

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

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.

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.

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 256 members 2,690 guests
Recent signins: