Forum Discussion

uscmea's avatar
uscmea
Frequent Visitor
5 years ago
Solved

Cumulative Sum

Hi,

I have three main measures in my data model:

 

sales = DISTINCTCOUNT(Sales[Product ID])

leads = DISTINCTCOUNT(Leads[Lead ID])
conv = IF([vacation_days]<7,[sales]/[leads],0)
 
The measure I want to do the cumulative is "conv", which depends on the number of vacation days that each employee has taken in a month. If the number of days is greater than 7, "conv" is going to be equal to 0%; if not, sales/leads.
When I try to do the cumulative sum of "conv", with the following formula:
 
cum_conv =
CALCULATE (
[conv],
FILTER (
ALL( 'Calendar'),
ISONORAFTER( 'Calendar'[Month Number]
,MAX ( 'Calendar'[Month Number] ),DESC
)
))
 
Instead of obtaining 42.86% in Month Number = 2, I obtain 0%.
 

 Here find attached the Power BI model. https://drive.google.com/file/d/1izQnlhw_geZtn4bfe5T-by3BB__mbwFK/view?usp=sharing

 

Thank you in advance for your help!

 

Best,
Sara

  • DataInsights's avatar
    DataInsights
    5 years ago

    uscmea,

     

    Try this measure:

     

    cum_conv = 
VAR vCurrentMonth =
    MAX ( 'Calendar'[Month Number] )
VAR vTable =
    ADDCOLUMNS ( ALLSELECTED ( 'Calendar'[Month Number] ), "@conv", [conv] )
VAR vResult =
    SUMX ( vTable, IF ( 'Calendar'[Month Number] <= vCurrentMonth, [@conv] ) )
RETURN
    vResult

     

     

4 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    uscmea,

     

    I wasn't able to access the pbix via the link. This is the basic pattern for a cumulative sum:

     

    cum_conv =
CALCULATE (
    [conv],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Month Number] <= MAX ( 'Calendar'[Month Number] )
    )
)

     

    • uscmea's avatar
      uscmea
      Frequent Visitor
      5 years ago

      Hi DataInsights,

       

      Thank you very much for your answer!

       

      I tried before this formula, but for some reason, it is not working.

       

      I also tried SUMX = (FILTER (ALL('Calendar'),'Calendar'[Month Number] <=MAX('Calendar'[Month Number]), [conv])), but I still do not obtain the desired result.


      I shared the Power BI model via Drive, let me know if you can download it from there. 

      Best regards,

      Sara

      • DataInsights's avatar
        DataInsights
        Super User
        5 years ago

        uscmea,

         

        Try this measure:

         

        cum_conv = 
VAR vCurrentMonth =
    MAX ( 'Calendar'[Month Number] )
VAR vTable =
    ADDCOLUMNS ( ALLSELECTED ( 'Calendar'[Month Number] ), "@conv", [conv] )
VAR vResult =
    SUMX ( vTable, IF ( 'Calendar'[Month Number] <= vCurrentMonth, [@conv] ) )
RETURN
    vResult