Forum Discussion
Aggregating Target Values that change over date ranges
Ok...I need some help. I am by no means an expert in Power BI and mostly self-taught through forums, videos and books from the Gurus. MattAllington...I have been reading Supercharge but have not formulated a solution yet.
I have daily production data (Actual by production line) that I compare to target production levels (Planned) and then displayed both graphically in a column chart (actual) vs. planned (line), as well as in a card as a ratio (actual over planned).
Recently, our team has been changing the target levels. I do not want to lose the historical ratios, so I have developed a table that contains the StartDate and EndDate for each target for each sku. These have a relationship to an ItemMaster table via ItemCode.
In order to reference these date ranges, I am using the following measure:
Planned Lbs =
CALCULATE (
SUMX ( DIM_Standards, [Target Lbs] ),
FILTER (
'DIM_Standards',
'DIM_Standards'[StartDate] <= VALUES ( DIM_Calendar[Date] )
&& 'DIM_Standards'[EndDate] >= VALUES ( 'dim_calendar'[date] )
)
)The Target Lbs measure referenced is:
Target Lbs = ( [Case Weight] * [Target Cases] )
Target Cases measure is a simply the average of the target from that column in the DIM_Standards table that has varying target levels per the date ranges based on StartDate and EndDate. For instance in the visual below, the standard for one of the skus on Tuesday could be different for the standard for the same sku on Saturday (due to crewing).
The "planned lbs" measure works fine in the visualiztion when I am drilled down and look at a week long period by date:
But when I drill up to a mothly level by week, "Planned Lbs" generates an error (in both the same visualition and any calcualtions using the "Planned Lbs" measure (ex. actual over planned ratio).
Any suggestions on how to modify the code for this measure to allow it function at both the individual date level and in aggregate over a month or year duration?
Thanks! Any help would be appreciated.