cancel
Showing results for
Did you mean:
Regular Visitor

## 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,

Resolver 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.

regards

rainer1

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors