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,

 

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