Forum Discussion
DAX code for comparing dates not working?
I have an open date and close date in my table, and have them related via inactive links to a date table. I historically have used the following rolling measures to capture opens/closures within flexible time periods such as the following:
Created 4XCycle Day Rolling Measure = Calculate( DISTINCTCOUNT(Table[Key]), USERELATIONSHIP('Date'[Date], Table[CreateLink]), FILTER(Table, Table[CreateLink]>=(Max('Date'[Date])-(4*[Cycle Length])) && Table[CreateLink]<Max('Date'[Date])))
Cycle Length is a numerical what if parameter that people can adjust.
This has stopped working in the past few weeks. Anything wrong that people can see?
- Solved it myself - for some reason it previously defaulted to "today" as a max on the dates. Now that has to be declared specifically so you need to do the following: Created 4XCycle Day Rolling Measure = Calculate( DISTINCTCOUNT(Table[Key]), USERELATIONSHIP('Date'[Date], Table[CreateLink]), FILTER(Table, Table[CreateLink]>=Min(([Todaysdate]-(4*[Cycle Length])),(Max('Date'[Date])-(4*[Cycle Length]))), FILTER(Table, Table[CreateLink]
3 Replies
hi, @CaseyJ
Just from a formula we could not get the issue.Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Please share your sample pbix file or some sample data and the expected output.
Do mask sensitive data before uploading.
Best Regards,
Lin
- The data is sensitive internal stuff. Date table is just the date table from https://github.com/sql-bi/DaxDateTemplate I'm just wondering if there's something wrong with my DAX syntax or something that would cause it to fail when the year flipped over, as I'm at a loss otherwise.
- Solved it myself - for some reason it previously defaulted to "today" as a max on the dates. Now that has to be declared specifically so you need to do the following: Created 4XCycle Day Rolling Measure = Calculate( DISTINCTCOUNT(Table[Key]), USERELATIONSHIP('Date'[Date], Table[CreateLink]), FILTER(Table, Table[CreateLink]>=Min(([Todaysdate]-(4*[Cycle Length])),(Max('Date'[Date])-(4*[Cycle Length]))), FILTER(Table, Table[CreateLink]
