Dynamic IF formula based on date range slicer

[Anonymous]

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!

[mattww · Accepted solution]

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.

[Anonymous]

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

[Anonymous]

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

[mattww · Accepted solution]

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 data

Matt

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

[mattww · Accepted solution]

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

[alavey]

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 

[mattww · Accepted solution]

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.