topic Cumulative Sum in DAX Commands and Tips

Cumulative Sum

uscmea — Fri, 09 Jul 2021 12:45:21 GMT

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

Re: Cumulative Sum

DataInsights — Sun, 27 Jun 2021 16:49:35 GMT

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

Re: Cumulative Sum

uscmea — Fri, 09 Jul 2021 12:49:15 GMT

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

Re: Cumulative Sum

DataInsights — Fri, 09 Jul 2021 22:17:23 GMT

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

Re: Cumulative Sum

uscmea — Sun, 11 Jul 2021 16:21:29 GMT

Thank you very much for your help, DataInsights !!