Count days between 2 dates in different columns for multiple entries

[vrawert]

Hi all,

I’m new to dax formulas (Power Pivot – BI – Query) and I’m trying to do some calculation in a dataset that I need to analyze.

My data consist of 2 columns of dates (start date of a discount period and the end date), the products that are on discount, the customer and the discount %.

What I would like to have is some measures to count the days of discounts for each product, product segment, customer, and manufactor as I want these dimensions to be in my slicers.

My question is how can I avoid duplicate count of each row when I need to get the discount days for the product segment and each segment has more than one product?

Also I have a Calendar table in which I have created an active relationship with the Start Date, and an inactive with the end date.

I use the Month from the calendar table as my slicer but cant get all the rows that either the start date or the end date is in the month I have selected in the slicer.

I have tried to do some calculation with the USERELATIONSHIP but I couldn’t get what I needed.

Any help will be much appreciated.

An example from my data:

Start Date	End Date	Segment	Manufactor	Customer	Product	Discount
18/10/2019	30/11/2019	Segment 3	Manufactor 1	Customer 2	Product 44	-19%
18/10/2019	30/11/2019	Segment 3	Manufactor 1	Customer 2	Product 45	-19%
18/10/2019	30/11/2019	Segment 3	Manufactor 1	Customer 2	Product 46	-18%
18/10/2019	30/11/2019	Segment 3	Manufactor 1	Customer 2	Product 47	-18%
18/10/2019	30/11/2019	Segment 3	Manufactor 1	Customer 2	Product 48	-18%
18/10/2019	30/11/2019	Segment 3	Manufactor 1	Customer 2	Product 49	-19%
04/11/2019	30/11/2019	Segment 2	Manufactor 6	Customer 2	Product 56	-9%
04/11/2019	30/11/2019	Segment 2	Manufactor 6	Customer 2	Product 57	-9%
07/11/2019	27/11/2019	Segment 2	Manufactor 3	Customer 1	Product 24	-25%
13/11/2019	27/11/2019	Segment 3	Manufactor 8	Customer 3	Product 69	-30%
13/11/2019	30/11/2019	Segment 2	Manufactor 1	Customer 7	Product 20	-20%
13/11/2019	30/11/2019	Segment 2	Manufactor 9	Customer 7	Product 147	-20%
13/11/2019	30/11/2019	Segment 2	Manufactor 9	Customer 7	Product 148	-20%
13/11/2019	30/11/2019	Segment 2	Manufactor 9	Customer 7	Product 149	-20%
13/11/2019	30/11/2019	Segment 2	Manufactor 9	Customer 7	Product 150	-20%
13/11/2019	30/11/2019	Segment 2	Manufactor 9	Customer 7	Product 151	-20%
13/11/2019	30/11/2019	Segment 2	Manufactor 15	Customer 7	Product 152	-15%

Thanks,

[rainer1]

Hi vrawert,

it's not possible to filter with an inactive relation, so if you want filter between start and end you need two slicers one for the start date and the other for the end. 

For the calculation for the day between the two dates you can use

Diff = DATEDIFF(MIN(Tabelle1[Start Date];Tabelle1[End Date]);MAX(Tabelle1[Start Date];Tabelle1[End Date]);DAY) 

I build up a small sample report for you based on your data.

I hope this will help you...

regards

rainer1