Forum Discussion

Gwhiz's avatar
Gwhiz
Icon for Helper I rankHelper I
7 years ago
Solved

Need help modifying dax formula for cumulative sumx

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(...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    You may download my solution PBI file from here.

    Hope this helps.

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
Gwhiz
Icon for Helper I rankHelper I
7 years ago

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.