Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Changing a date formula with a visual filter

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.
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

yingyinr_1-1673599217802.png

Best Regards

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

yingyinr_1-1673599217802.png

Best Regards

amitchandak
Super User
Super User

@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)) )
Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.