Changing a date formula with a visual filter

[Juleeees]

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.

[v-yiruan-msft · Accepted solution]

Hi @Juleeees ,

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

[amitchandak]

@Juleeees , 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)) )