Forum Discussion

leo's avatar
leo
Frequent Visitor
9 years ago
Solved

Holiday Item Sales with variable date range

I am gaining speed with Power Bi, DAX, and M but this problem is well beyond my abilities.    The business sells specific items that are related to a holilday.  Each holiday has a specific date ran...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi leo,

     

    If I understand you correctly, you should be able to use the formula below to create a new measure to calculate the sales filtered by the holiday date range and the items for that holiday.

    Total Sales =
CALCULATE (
    SUM ( Sales[SalesAmount] ),
    FILTER (
        ALL ( Sales ),
        (
            Sales[Date] >= MAX ( Holidays[HolidayBeginDate] )
                && Sales[Date] <= MAX ( Holidays[HolidayEndDate] )
        )
            && CONTAINS ( VALUES ( Items ), Items[ItemCode], Sales[ItemCode] )
    )
)

    Then you can show this measure with item category (or item) on a chart/table visual in this scenario. :smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi leo,

My goal is to use Power BI to allow the user to analyze sales at the item level across holidays and at the holiday level across items. To make things a little tougher, the users would like the ability to alter the holiday date ranges as part of the analysis.

I am not sure I understand your requirement totally. Could you be more precisely with it by posting your expected results? :smileyhappy:

 

Regards

leo's avatar
leo
Frequent Visitor
9 years ago

Thanks for taking a look.

I would like to have a slider for holiday code (from Holidays table).  When a single holiday is selected, a column chart would display sales by item category (or item) and a table would display the sales by item for the selected holiday.  The sales would be filtered by the holiday date range (based on the Sales table) and the items for that holiday (in the HolidayItem table).  

 

Perhaps a DAX command could be used to create a table (containing item, item category, and sales amount) for the selected holiday.

 

 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi leo,

     

    If I understand you correctly, you should be able to use the formula below to create a new measure to calculate the sales filtered by the holiday date range and the items for that holiday.

    Total Sales =
CALCULATE (
    SUM ( Sales[SalesAmount] ),
    FILTER (
        ALL ( Sales ),
        (
            Sales[Date] >= MAX ( Holidays[HolidayBeginDate] )
                && Sales[Date] <= MAX ( Holidays[HolidayEndDate] )
        )
            && CONTAINS ( VALUES ( Items ), Items[ItemCode], Sales[ItemCode] )
    )
)

    Then you can show this measure with item category (or item) on a chart/table visual in this scenario. :smileyhappy:

     

    Regards

    • leo's avatar
      leo
      Frequent Visitor
      9 years ago

      Thanks v-ljerr-msft.  

       

      This was a great help.  I wasn't sure how to approach the DAX or whether to create a table or a measure.

       

      I made one tweek to your solution.  The contains was used with the HolidayItem table which defines which items appear in each holiday.  The Values statement then needed both the holiday and the item values.

       

      Holiday Sales =
      CALCULATE(
           SUM( Sales[Inv Profit] ),
                FILTER(
                     ALL( Sales ),
                     (
                          Sales[Inv Date] >= MAX( Holidays[Hol Begin Date] )
                               && Sales[Inv Date] <= MAX( Holidays[Hol End Date] )
                     )
                        && CONTAINS( VALUES( HolidayItems ), [ItemSK], Sales[ItemSK], HolidayItems[HolidaySK], MAX( Holidays[HolidaySK] )           )
      )
      )