Using date selected in a calculated column

[EUGENEG]

Good day 

I currently have a situation were I am required to allow a report to filter via a date selection ie:The OrderDate

I have a calculated column that uses the "datediff" and "today" functions however I want to subsitute the "Today" function with the date selected on the date filter 

Calculated Column as below 

Sales = IF(DATEDIFF(SALES[ORDERDATE].[Date],TODAY(),DAY)>=30
&& (DATEDIFF(SALES[ORDERDATE].[Date],TODAY(),DAY)<60),CALCULATE(COUNT(SALES[ORDERREFERENCE])))

[v-lili6-msft · Accepted solution]

hi @EUGENEG 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, for your case, you could use a measure as below:

Sales =
IF (
    DATEDIFF ( MAX(SALES[ORDERDATE].[Date]), MAX(Date[Date]), DAY ) >= 30
        && ( MAX(DATEDIFF ( SALES[ORDERDATE].[Date]), MAX(Date[Date]), DAY ) < 60 ),
    CALCULATE ( COUNT ( SALES[ORDERREFERENCE] ) )
)

Date[Date] is a slicer

Then drag all fields from SALES table and this measure [Sales] into a table visual.

Regards,

Lin

[amitchandak]

I doubt column But Measure can be

Measure = 
var _max = maxx('Calendar',('Calendar'[Date]))
return
DATEDIFF(FIRSTNONBLANK(salesorder[DATE],TRUE()),_max,MONTH)

[Anonymous]

Calculated columns can not be dynamic, they are getting loaded when you are loading your dataset for first time.

You can try measure 

 I recommend you to create one dummy calendar date table which is not connected with any other table in your model.

Use 'calendar date'[Date] column in slicer and make it your slicer as after slicer or between slicer.

then update your sales measure by replacing today() with min('calendar date'[Date])

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.