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

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
Icon for Microsoft Employee rankMicrosoft 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] )           )
    )
    )