Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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
You can filter your graph and your table in report - "visualizations": click on fields--> filters: "weeks_to_convert" is less than 8
ciao
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |