Hello there! Not sure if you can get the issue from the title, so I am elaborating a bit more below:
Our business case is that our model has multiple records of events with start and end dates. Events have 3-4 dimensions: Retailer,Brand, product category and discount rate.
Our main reports aggregate the events information in order to give business insights.
I am having the following challenge, when I am trying to deal with overlapping events. This means events that refer to same brand, category and retailer, in which case I don’t want to double count their duration. For example if brand X is having an event in retailer Y from Jan 1st to Jan10th and also one from Jan5th to Jan15th, I would like to receive as aggregate result 15 promodays and not 10+10=20 promodays.
The way I have dealt with this issue is the following DAX measure (not sure if optimum, but works when I view data per retailer)
Net_Promodays = COUNTROWS (FILTER(Dates, CALCULATE((COUNTROWS( FILTER( Events, Events[Start date] <= MAX(Dates[Date]) && Events[End date] >= MAX(Dates[Date]) ) ))> 0) ))
Where the problem comes up is when I aggregate totals per brand or even super totals where I get the below
On the left side I get the wrong totals - I should get the sum of the right table, which are the net promodays for a specific brand an each retailer.
Would you have any idea on how to tackle both with 1 logic?
Attached the pbix fileDemo_SoA_Jun19_FD.pbix (958.7 KB)
Thanks & you are the best!