Holiday Item Sales with variable date range

[leo]

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. 

[v-ljerr-msft]

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? 

Regards

[leo]

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 · Accepted solution]

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. 

Regards

[leo]

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] )           )
)
)