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 range and set of items.  An item could be sold in multiple holidays.  Over 100 holidays are tracked.

 Sample Holiday table

Sample holiday/item table

 

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.

 

 Any suggestions on the approach would be greatly appreciated. 

  

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

4 Replies

  • 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