Cumulative Sum

[uscmea]

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]

@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]

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 · Accepted solution]

@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

[uscmea]

Thank you very much for your help, @DataInsights !!