Dynamic IF formula based on date range slicer

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

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

 

Matt