Forum Discussion
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!
Hi Anonymous ,
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_Key Date Period 20211101 01-11-2021 P1 20211102 02-11-2021 P1 20211103 03-11-2021 P1 20211201 01-12-2021 P2 * 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.
Hey Anonymous,
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
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 dataMatt
5 Replies
mattww Thanks a lot, it's working! 🙂
Hi Anonymous ,
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_Key Date Period 20211101 01-11-2021 P1 20211102 02-11-2021 P1 20211103 03-11-2021 P1 20211201 01-12-2021 P2 * 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.
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).Hey Anonymous,
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
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 dataMatt
Hi Matt,
Would you be able to explain how you were able to create a table to capture your date selection?
I would like to use your solution above for a project.
Thanks,
Abigail
