Forum Discussion
Count days between 2 dates in different columns for multiple entries
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,
1 Reply
rainer1Resolver III
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