Filtered Date

[JB17]

Hi,

Can you help me how to translate below underlined statement in DAX:

      Is before today and is after September 30, 2024 

I know this can be done in filters but I wanted to avoid manual date set up everytime I will refresh the reports. Thank you so much in advance! 

[rohit1991 · Accepted solution]

Hi @JB17 

You can do this easily with a small DAX formula instead of setting manual filters every time.

Could you please try below Steps:

1. Create a calculated column or measure with this DAX:
2. Add this to your visual-level filters and set it to FilteredDate = 1.

FilteredDate =
VAR _today = TODAY()
VAR _cutoff = DATE(2024, 9, 30)
RETURN
IF(
    'Date'[Date] < _today &&
    'Date'[Date] > _cutoff,
    1
)

[Rufyda · Accepted solution]

Hi @JB17 
FilteredDate =
VAR _today = TODAY()
VAR _cutoff = DATE(2024, 9, 30)
RETURN
          IF ( 'Date'[Date] < _today && 'Date'[Date] > _cutoff, 1, 0 )

You can learn more here:

TODAY function (DAX): https://learn.microsoft.com/en-us/dax/today-function-dax

DATE function (DAX): https://learn.microsoft.com/en-us/dax/date-function-dax

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Regards,
Rufyda Rahma | Microsoft MIE
https : https://www.linkedin.com/in/rufyda-abdelhadirahma/
Blog : https://medium.com/@rufydarahma

[Kedar_Pande · Accepted solution]

'Table'[Date] < TODAY() && 'Table'[Date] > DATE(2024, 9, 30)

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[v-kpoloju-msft · Accepted solution]

Hi @JB17,

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @Kedar_Pande, @Rufyda, @rohit1991, for those inputs on this thread. I reproduced the scenario, and it worked on my end. I used it as sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it.

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @JB17,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @JB17,

Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[Kedar_Pande · Accepted solution]

'Table'[Date] < TODAY() && 'Table'[Date] > DATE(2024, 9, 30)

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[Rufyda · Accepted solution]

Hi @JB17 
FilteredDate =
VAR _today = TODAY()
VAR _cutoff = DATE(2024, 9, 30)
RETURN
          IF ( 'Date'[Date] < _today && 'Date'[Date] > _cutoff, 1, 0 )

You can learn more here:

TODAY function (DAX): https://learn.microsoft.com/en-us/dax/today-function-dax

DATE function (DAX): https://learn.microsoft.com/en-us/dax/date-function-dax

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Regards,
Rufyda Rahma | Microsoft MIE
https : https://www.linkedin.com/in/rufyda-abdelhadirahma/
Blog : https://medium.com/@rufydarahma

[rohit1991 · Accepted solution]

Hi @JB17 

You can do this easily with a small DAX formula instead of setting manual filters every time.

Could you please try below Steps:

1. Create a calculated column or measure with this DAX:
2. Add this to your visual-level filters and set it to FilteredDate = 1.

FilteredDate =
VAR _today = TODAY()
VAR _cutoff = DATE(2024, 9, 30)
RETURN
IF(
    'Date'[Date] < _today &&
    'Date'[Date] > _cutoff,
    1
)