cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saba2021
Frequent Visitor

Dynamic IF formula based on date range slicer

Hi there!

 

I hope someone can help me here.

I want to create a table that states whether a product was purchased within a certain period. The period would depend on what date the user chooses in the date range slicer. So I'm thinking of something like this:

If (product) is in (selected date range), then "yes", else "no"

 

Does this make sense?

Thanks a lot for your help!

2 ACCEPTED SOLUTIONS
mattww
Super User
Super User

Hi @saba2021 ,

 

This should be achievable with a normal calendar table, so if you have a calendar with one row per day, including a date column plus another column indicating the period

 

Date_KeyDatePeriod
2021110101-11-2021P1
2021110202-11-2021P1
2021110303-11-2021P1
2021120101-12-2021P2

* sorry the formatting on this table isn't behaving, and HTML isn't my thing!


Add the Date column to your slicer, and the Period column to your visual (bar chart, table, whatever).

 

If this is joined to your Sales table based on the Sales date (the key ideally), a simple COUNTROWS([SalesTable]) or DISTINCTCOUNT([CustomerId]) measure would work in your visual, to count up how many of a given product (another slicer presumably) were sold in each period.

 

Let me know if that doesn't make sense or if I'm not understanding what you're aiming for

 

Matt

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hey @saba2021,

 

Ah ok, I get you. 

 

So I think the problem you're going to have is an attribute like Yes / No would naturally be something like a Calculated Column, but these are only calculated when the report is refreshed, so would not be responsive to slicer changes.

 

Measures on the other hand would be responsive, but only really apply in aggregate, you can't create a measure which applies an attribute to each row.

 

You could get around this by having two date tables, one (unrelated) table to capture your date selection and another standard date table attached to your Sales

 

Relationships.PNG

 

Now you will be able to use CALCULATE to count up (or sum up) any rows in the Sales table where the date is within the selected range

 

Count_Sales = COUNTROWS(Fact_Sales)

Count_Sales_SelectedPeriod = CALCULATE(Fact_Sales[Count_Sales],ALL(Dim_SalesDate),Dim_SalesDate[Date] >= MIN(Dim_SelectedDate[Date]),Dim_SalesDate[Date] < MAX(Dim_SelectedDate[Date]))
 
This count could then be used in an IF formula if you needed some kind of indicator on each Sales row.
 
IsInPeriod = IF(Fact_Sales[Count_Sales_SelectedPeriod] > 0,"Yes","No")
 
See samples below showing two different date selections on some simple sample data
 
DateSlicer1.PNGDateSlicer2.PNG

Matt

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
saba2021
Frequent Visitor

@mattww Thanks a lot, it's working! 🙂 

saba2021
Frequent Visitor

Hi Matt,

 

Thanks for your reply!

 

I'm not sure I understand you correctly. I'm not looking for a measure to sum up the amount that was sold within a certain period. Instead, I'm looking for a "yes/no" result. My current formula is:


Product Sold =
IF(Product[Last Time It Was Sold].[MonthNo] >= MONTH(TODAY())-3, "Product Sold", "Product Not Sold")
 
However, instead of just stating whether a product was sold within the last 3 months, I want the date range to depend on what the user of the dashboard chooses in the date range slicer. So it could be within the past 3 days, 4 months, 6 months, 2 years etc., whatever date range the user selects. I should also mention that the date ranger slicer is in the DD.MM.YYYY format (s. below).
 
Date Range Slicer.PNG

Hey @saba2021,

 

Ah ok, I get you. 

 

So I think the problem you're going to have is an attribute like Yes / No would naturally be something like a Calculated Column, but these are only calculated when the report is refreshed, so would not be responsive to slicer changes.

 

Measures on the other hand would be responsive, but only really apply in aggregate, you can't create a measure which applies an attribute to each row.

 

You could get around this by having two date tables, one (unrelated) table to capture your date selection and another standard date table attached to your Sales

 

Relationships.PNG

 

Now you will be able to use CALCULATE to count up (or sum up) any rows in the Sales table where the date is within the selected range

 

Count_Sales = COUNTROWS(Fact_Sales)

Count_Sales_SelectedPeriod = CALCULATE(Fact_Sales[Count_Sales],ALL(Dim_SalesDate),Dim_SalesDate[Date] >= MIN(Dim_SelectedDate[Date]),Dim_SalesDate[Date] < MAX(Dim_SelectedDate[Date]))
 
This count could then be used in an IF formula if you needed some kind of indicator on each Sales row.
 
IsInPeriod = IF(Fact_Sales[Count_Sales_SelectedPeriod] > 0,"Yes","No")
 
See samples below showing two different date selections on some simple sample data
 
DateSlicer1.PNGDateSlicer2.PNG

Matt

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

mattww
Super User
Super User

Hi @saba2021 ,

 

This should be achievable with a normal calendar table, so if you have a calendar with one row per day, including a date column plus another column indicating the period

 

Date_KeyDatePeriod
2021110101-11-2021P1
2021110202-11-2021P1
2021110303-11-2021P1
2021120101-12-2021P2

* sorry the formatting on this table isn't behaving, and HTML isn't my thing!


Add the Date column to your slicer, and the Period column to your visual (bar chart, table, whatever).

 

If this is joined to your Sales table based on the Sales date (the key ideally), a simple COUNTROWS([SalesTable]) or DISTINCTCOUNT([CustomerId]) measure would work in your visual, to count up how many of a given product (another slicer presumably) were sold in each period.

 

Let me know if that doesn't make sense or if I'm not understanding what you're aiming for

 

Matt

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors