Forum Discussion

texag95's avatar
texag95
Regular Visitor
8 years ago

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.

5 Replies

  • Hi texag95,

     

    Please post  a sample data and a little description of the columns/tables to be used.

    • texag95's avatar
      texag95
      Regular Visitor

      danextian sorry for the delay in a response....been a busy week.  Thanks for the inquiry.  Here are some of the items you requested.

       

      DIM_Calendar = CALENDARAUTO(12)

       

      • texag95's avatar
        texag95
        Regular Visitor

        Just noticed the typo in Row 4 of the sample DIM_Standards table.  ItemCode 1 should have a StartDate of 5/20/2018 instead of 2028.