Forum Discussion

Saqibmughal00's avatar
Saqibmughal00
Icon for Helper I rankHelper I
1 year ago
Solved

Help in counting cumulative for each category separately

Hi, 

 

I am trying to create a cumulative value for each category as pictured. I tried below DAX but its not giving me cumulative but a weekly data again.

"

Cumulative C&I Earned Hours =
CALCULATE(
    [C&I Earned Hours],
    FILTER(
        ALLSELECTED('C&I PROGRESS FOR PF ANALYSIS'[Value]),
        ISONORAFTER('C&I PROGRESS FOR PF ANALYSIS'[Value], MAX('C&I PROGRESS FOR PF ANALYSIS'[Value]), DESC)
    )

"

 

can someone Help please

 

 

  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    Hi Saqibmughal00, 
    Thank you for reaching out to the Microsoft fabric community forum. Thank you maruthisp, for your input on this issue 
     
     After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.   

      

    Dax Measure: 

    Cumulative Value by Description = 
VAR CurrWeek = MAX(CIPFAnalysis[Week Ending])
VAR CurrDesc = SELECTEDVALUE(CIPFAnalysis[Description])
RETURN
CALCULATE(
    SUM(CIPFAnalysis[Value]),
    FILTER(
        ALLSELECTED(CIPFAnalysis),
        CIPFAnalysis[Week Ending] <= CurrWeek &&
        CIPFAnalysis[Description] = CurrDesc
    )
)

    Output: 

    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
     
     Thank you.

       

4 Replies

  • maruthisp's avatar
    maruthisp
    Icon for Super User rankSuper User
    1 year ago

    Hi Saqibmughal00  ,

    I hope you have created a measure for EarnedHours, something like below:
    EarnedHours =
    SUM( CIPFAnalysis[EarnedHours] )

    Then, I am creating cumulative based on the above measure with some range:
    CumulativeEarnedHours =
    VAR CurrCategory = SELECTEDVALUE( CIPFAnalysis[Category] )
    VAR CurrWeek = MAX( CIPFAnalysis[WeekEnding] )
    RETURN
    CALCULATE(
    [EarnedHours],
    FILTER(
    ALLSELECTED( CIPFAnalysis ),
    CIPFAnalysis[Category] = CurrCategory
    && CIPFAnalysis[WeekEnding] <= CurrWeek
    )
    )

    If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

     

    Best Regards, 

    Maruthi 

    LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

    X            -  Maruthi Siva Prasad - (@MaruthiSP) / X






    • Saqibmughal00's avatar
      Saqibmughal00
      Icon for Helper I rankHelper I
      1 year ago

      It didnt work and My measure for earned hours is as below:

       

      • v-saisrao-msft's avatar
        v-saisrao-msft
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi Saqibmughal00, 
        Thank you for reaching out to the Microsoft fabric community forum. Thank you maruthisp, for your input on this issue 
         
         After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.   

          

        Dax Measure: 

        Cumulative Value by Description = 
VAR CurrWeek = MAX(CIPFAnalysis[Week Ending])
VAR CurrDesc = SELECTEDVALUE(CIPFAnalysis[Description])
RETURN
CALCULATE(
    SUM(CIPFAnalysis[Value]),
    FILTER(
        ALLSELECTED(CIPFAnalysis),
        CIPFAnalysis[Week Ending] <= CurrWeek &&
        CIPFAnalysis[Description] = CurrDesc
    )
)

        Output: 

        If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
         
         Thank you.