Forum Discussion

pbiforum123's avatar
pbiforum123
Icon for Post Patron rankPost Patron
4 years ago

Sum to Cumulative Sum

Hello All,

How can I convert the below DAX to Cumulative Sum instead of Sum. Can someone please help me...

 

Delivered =
VAR Enddate =
IF ( MAX ( DimDate[Date] ) >= TODAY (), TODAY (), MAX ( DimDate[Date] ) )
RETURN
CALCULATE (
SUM ( FACT[Capacity] ),
FACT[ActualEnd] <= Enddate,
FACT[ID] = 22,
FACT[ActualEnd] >= MIN ( DimDate[Date] )
)

Need Help

8 Replies

  • PC2790's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Try:

     

    CumulativeSales = CALCULATE(SUM ( FACT[Capacity] ),FILTER(ALLSELECTED(DatesTable[Date]),DatesTable[Date]<=Max(DatesTable[Date])))
     
    • pbiforum123's avatar
      pbiforum123
      Icon for Post Patron rankPost Patron
      4 years ago

      Thanks a lot for responding but how can apply the filters that has been applied in my DAX. If you please convert the changes to DAX it would be lot better if you have sometime.

      • PC2790's avatar
        PC2790
        Icon for Community Champion rankCommunity Champion
        4 years ago

        See if this is working for you:

         

        CumulativeSales =
VAR Enddate =
    IF ( MAX ( DimDate[Date] ) >= TODAY (), TODAY (), MAX ( DimDate[Date] ) )
RETURN
    CALCULATE (
        SUM ( FACT[Capacity] ),
        FILTER ( ALLSELECTED ( DimDate[Date] ), DimDate[Date] <= Enddate ),
        FACT[ID] = 22,
        FACT[ActualEnd] >= MIN ( DimDate[Date] )
    )
  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    See if it works better without the startdate filter you have:

    Delivered =
VAR Enddate = MIN ( TODAY (), MAX ( DimDate[Date] ) )
RETURN
    CALCULATE (
        SUM ( FACT[Capacity] ),
        FACT[ActualEnd] <= Enddate,
        FACT[ID] = 22
    )
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi. pbiforum123 ;

    You could try it.

    Delivered =
VAR Enddate =
    IF ( MAX ( DimDate[Date] ) >= TODAY (), TODAY (), MAX ( DimDate[Date] ) )
RETURN
    CALCULATE (
        SUM ( FACT[Capacity] ),
        FILTER (
            ALLSELECTED ( FACT ),
            FACT[ActualEnd] <= Enddate
                && FACT[ID] = 22
                && FACT[ActualEnd] >= MIN ( DimDate[Date] )
        )
    )

    Or can you share a simple example and the result what you want to output?


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, pbiforum123 ;

    Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.