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 All,
I'm trying to calculate the cumulative total but want to limit it as per max actual date and I don't want the cumulative total to be carried forward after 18/05/2023, it should only show up to 6.70.
Thank you
Solved! Go to Solution.
Hi @cool_333 ,
You could create a measure.
Cum.Daily Acutal Units = IF(ISBLANK(MAX('Table'[Daily Acutal Units Total])),BLANK(),CALCULATE(SUM('Table'[Daily Acutal Units Total]),FILTER(ALL('Table'),[Date]<=MAX('Table'[Date]))))
I use the IF statement to determine whether the value is null or not, and if it is not null, it returns a cumulative value. In your case, you need to use the ISBLANK function, not the BLANK function.
You could also download the attachment for mroe details.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have run into issues witht the same situation where using your equations did not work on my data.
If instead of the 0 value entries, you dont' have one (common if you are grabbing data from an entry system, it may not happen every day) and plotting Month-Day for date under hiearchy, instead of letting PBI autoscale for you, you will see gaps in your graphs.
This is what I am trying to prevent.
Hi @cool_333 ,
You could create a measure.
Cum.Daily Acutal Units = IF(ISBLANK(MAX('Table'[Daily Acutal Units Total])),BLANK(),CALCULATE(SUM('Table'[Daily Acutal Units Total]),FILTER(ALL('Table'),[Date]<=MAX('Table'[Date]))))
I use the IF statement to determine whether the value is null or not, and if it is not null, it returns a cumulative value. In your case, you need to use the ISBLANK function, not the BLANK function.
You could also download the attachment for mroe details.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can grab the max date with a value and use that as part of the filter arguments.
I'm assuming that Daily actual total units is a column and not a measure.
Cumulative daily total =
var maxDateWithDailyActualTotalUnits = CALCULATE(MAX([Date]), NOT(ISBLANK([Daily Actual Total Units])))
// your calculations
return CALCULATE(
// your calculations for cumulative sum
&& [Date] <= maxDateWithDailyActualTotalUnits
)
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 |
---|---|
38 | |
26 | |
20 | |
15 | |
8 |
User | Count |
---|---|
69 | |
47 | |
46 | |
20 | |
16 |