cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Helper III

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

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

Helper III

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)

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:

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.

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

Helper III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors