Refer to A Date Filter

[ninsights]

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.

[Anonymous]

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

[ninsights]

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]

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

[ninsights]

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]

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.
Please give Kudos for support.

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

[ninsights]

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