Reply
ninsights
Helper III
Helper III

Refer to A Date Filter

I have a filter on a report for a variable called DATE. The user selects the start and end of the date range for the variable DATE.

 

Based on their selection, I would like a formula that calculates the number of days in the range they selected. For example: Days in Range = DateDiff(startDate, endDate, days). 

 

My question is,how do I pass the user's selected start date and end date into the formula for Days in Range?

 

If it matters, I have Power BI desktop connected to a SQL server. Thank you for your time.

6 REPLIES 6
avatar user
Anonymous
Not applicable

HI

Do a MAX and MIN

DateDiff(Min(table[date],max(table[Date]))

 

Thank you

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

 

That was what I originally tried, but that calculates the days in my range on a row by row basis. 

 

To further explain my example, suppose I sell 10 of Product 1 and 10 of product 2 between November 1 and November 5. I select November 1 and November 5 as th start and end date in my date filter.

 

Using the formula you suggested, if Product 2 did not sell any units on November 1, I will get:

 

Product 1, Quantity = 10, Days in Range = 5

Product 2, Quantity = 10, Days in Range = 4

 

I am looking to always have the Days in Range = 5 which is the number of days between the user's selected start and end date.

 

Product 1, Quantity = 10, Days in Range = 5

Product 2, Quantity = 10, Days in Range = 5

avatar user
Anonymous
Not applicable

Hi 

Did you try to edit the interaction between the date slicer and the other visuals? I think the issue is that your calculation is filtering the data based on the dates it has data.  You will need to modify your calculation for Quantity to be:

calculate Qty = calculate(sum(table[quantity]), filter(all('Date'),'Date'[date] >= MIN('Date'[date]) && 'Date'[date] <=MAX('Date'[date])))

 

Hope this help

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

I don't have other visuals. This file has only one visual which lists products and how many sold within the date range that the user picked in the date filter.

Product 1  Quantity 10   (which is correct)

Product 2  Quantity 10  (which is correct)

 

I am fairly new to Power BI so I don't know if your reference to a "slicer" is the same as a filter. On the Power BI desktop screen, my date selection is in a pane called "Filters - Filters for this Visual."

 

Based on the users start and end date in the filter (in the filters pane), I just want to calculate how many days are in the range they picked, not how many days are in the range that each product sold. The resulting display, if they picked November 1-5 in the date filter, should be:

 

Product 1  Quantity 10   Days in Range = 5 (regardless of whether this product sold on each of the 5 days or not)

Product 2  Quantity 10   Days in Range = 5  (regardless of whether this product sold on each of the 5 days or not)

 

avatar user
Anonymous
Not applicable

Hi

Let me try again.

On the tab on your report you might have two visuals:

1. the Slicer (Date selection)

2. A table that displays Product 1  , Quantity, Days in Range

 

Step to walk thru:

Disable Interaction.png

 

These steps will disable the interaction between the slicer and the table.

Once you done this,

Drop the following in your table

A. Product field

B. Quantity Field:   Calculate (sum(quantity), Filter(all('Date'), 'Date'[Date] >= Min('Date'[Date])  && 'Date'[Date]<= Max('Date'[Date])))

C. The calculated measure DateDiff that send earlier.

 

If I answer your question, please mark my post as a solution, this will also help others.
Please give Kudos for support.

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

 

Unfortunately, removing the interaction between the date slicer (where 5 days are selected) and the table that contains multiple columns about my products (including quantity) makes the table visualization think that the MIN date and MAX date are the full range in my data set (20 year's worth of data).

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)