topic Re: Changing a date formula with a visual filter in DAX Commands and Tips

Changing a date formula with a visual filter

Anonymous — Thu, 12 Jan 2023 10:20:22 GMT

Hi,

I want to change this formular by a visual filter:

Difference Days to Overdue = IF([Progress] = "Finished", BLANK(),DATEDIFF([Due Date (DD/MM/YYYY)], MIN('Calendar'[Date]) ,DAY))

The one marked in red should be a selectable value from the created table Calendar. I want a visual filter where you can select a certain range and then the minimum of this range is selected.

When I currently insert a visual filter of Calendar[Date] into my report page, the formula does not filter with.

Thanks in Advantage.

Re: Changing a date formula with a visual filter

amitchandak — Thu, 12 Jan 2023 11:45:36 GMT

Anonymous , Then you have create a measure

example

Difference Days to Overdue = Sumx(Table, IF([Progress] = "Finished", BLANK(),DATEDIFF([Due Date (DD/MM/YYYY)], MIN('Calendar'[Date]) ,DAY)) )

Re: Changing a date formula with a visual filter

Anonymous — Fri, 13 Jan 2023 08:42:11 GMT

Hi Anonymous ,

What you are trying to create is a calculated column? If what you created is a calculated column, it will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a slicer using [Date] field of 'Calendar' table

2. Create a measure as below to get the dynamic Difference Days by the filter/slicer...

Difference Days to Overdue = IF ( SELECTEDVALUE ( 'Table'[Progress] ) = "Finished", BLANK (), DATEDIFF ( SELECTEDVALUE ('Table'[Due Date (DD/MM/YYYY)] ), MIN ( 'Calendar'[Date] ), DAY ) )

Best Regards