cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EUGENEG
Helper I
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
v-lili6-msft
Community Support
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.

View solution in original post

3 REPLIES 3
v-lili6-msft
Community Support
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.

View solution in original post

amitchandak
Super User IV
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)

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Anonymous
Not applicable

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors