cancel
Showing results for
Did you mean:
Helper I

## Using date selected in a calculated column

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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User IV

I doubt column But Measure can be

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

Proud to be a Super User!

Anonymous
Not applicable

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

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

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks