I have three variables of output data, measured in terms of actuals and targets per month. The data is collected for 3 years, and for the current year, the target values are available for the entire calendar year. For example, all three variables actual values are available until March 2023, but their targets are available until December 2023. To find out the variance until April 2023 between actuals and targets, what DAX formula needs to be applied? Currently, the cumulative targets in the table are shown for each month until December. I don’t want to see the values of cumulative run for all months, it should auto fill based on the actuals of each month
cpcbahnp practuce.pbix (120.6 KB)
Dear @smith.delores
Welcome on the forum.
Two solutions are possible to get lose of the months in 2023 without actuals yet.
- Use a date slicer ending at the actual period, this is the most easy, as it limits the target and YTD to this period, no measure adjustments needed)
- Use blank() in the measures of Target and % Var when no actuals available
PBIX file attached with the workout attached:
cpcbahnp practuce v2.pbix (123.9 KB)
Ad 1 Details of the date slicer
Use with style “before”
Result:
Ad 2 Alternative is using Blank() in the measure-formula.
Total CP Targv2 = if([Total CP Act] = BLANK(), BLANK(),sum(Sheet1[CP-Target]))
% CP Varv2 = if([Total CP Act] = BLANK(), BLANK(),DIVIDE([Total CP Act],[Total CP Targv2],0) -1)
CP Targ_YTDv2 = if( [Total CP Act] = BLANK(),BLANK(),TOTALYTD([Total CP Targ],'Date table'[Date]))
Disadvantage of not using a date slicer is that the total target will appear for 2023, in this matrix table, as year 2023 is selected and contains the whole year target.
PS. It is possible to get rid of the Total row totals in a Matrix, as it does not make sence to present 3 years actuals as a total :
=== Click the matrix table, go to Visualizations – Format visual — Row Grand Total — Make Text color WHITE like the background and Apply to labels to " On " to white out the label as well.
Kind regards, Delta Select
Thank you very much for the solution
Simple one with Slicer as well, DAX. Both are working.
