Custom time periods in DAX

0
votes

I have a huge table where entries have product categories, create date, modify date, etc. Marketing in our company is launching campaigns in several "Promotional Periods" which are gathered in table with: Product category, Promotion name, start date, end date

I need to find a way to filter sales by promotional period. Can You please advice should I add some column in query editor, or rather calculated column with DAX which would add the Promotion name based on product category and creation date in range between start and end. Example code would be more than appreciated.

1
timepowerbidaxperiod

1 Answers

1
votes

I guess your promotional periods can overlap. Here an example with a period table:

datamodel:

Datamodel

Measure: We filter the date table, only on the records between the period start and end dates.

Total Amount Period:=Calculate(
[Total Amount],
    FILTER('Date',
            COUNTROWS(FILTER('Period',
                    Period[PeriodStart] <='Date'[DateValue] &&
                    Period[PeriodEnd] >= 'Date'[DateValue] ))
            > 0)
    )

The solution file (excel 2016): ExcelFile