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

Need Help

8 Replies

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

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

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