Cumulative Total for Historical Weeks Only

[scott01]

Hi,

I have calculated a running total measure that measures the number of accounts that have converted by each week since registration. This measure calculates over a user-defined window registration period and ensures that all accounts have had the same length of time to convert.

However, I'd like to be able to have the running total measure calculate only up until the maximum_weeks_to_convert value, in this case where weeks_to_convert = 7, in order to prevent the horizontal line at the end of the cumulative total.

Please can you assist in helping me to remove the values from the red highlighted areas?

Thanks

Running Total = 
var latest_week = MAX(ConversionData[current_week])
var latest_selected_week = CALCULATE(MAX(WeekList[business_week]),ALLSELECTED(WeekList[business_week]))
RETURN
var maximum_weeks_to_convert = latest_week - latest_selected_week
RETURN
CALCULATE(SUM(ConversionData[CY]),FILTER(ALLSELECTED(ConversionData),ConversionData[weeks_to_convert] <= maximum_weeks_to_convert && ConversionData[weeks_to_convert] <= MAX(ConversionData[weeks_to_convert])))

Sample dashboard:

Sample Conversion Example

[FlaF]

You can filter your graph and your table in report - "visualizations": click on fields--> filters: "weeks_to_convert" is less than 8

ciao

[v-yuezhe-msft · Accepted solution]

@scott01,

Change your DAX to the following:

Running Total = 
var latest_week = MAX(ConversionData[current_week])
var latest_selected_week = CALCULATE(MAX(WeekList[business_week]),ALLSELECTED(WeekList[business_week]))
RETURN
var minimum_weeks_to_convert = latest_week - latest_selected_week
RETURN
IF(ISBLANK([CY_full_opp_verbose]),BLANK(),CALCULATE(SUM(ConversionData[CY]),FILTER(ALLSELECTED(ConversionData),ConversionData[weeks_to_convert] <= minimum_weeks_to_convert && ConversionData[weeks_to_convert] <= MAX(ConversionData[weeks_to_convert]))))

Please check details in the PBIX file.
https://1drv.ms/u/s!AhsotbnGu1Nok1_mKj_sx7X_VqDF

Regards,
Lydia

[scott01]

@v-yuezhe-msft Thanks. That works perfectly.