Forum Discussion
Compare data between 2 dates
Hi BItoken ,
Since you have many other filters on the table visual, so all(table) will have some issue. Please use the following measure for instead:
Measure new = IF(MAX(renewals_history[eff_start_dt]) = CALCULATE(MAX('renewals_history'[eff_start_dt]),FILTER(ALL(renewals_history[eff_start_dt]),'renewals_history'[eff_start_dt]<=MAX('Date'[Date]))),1,0)If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi AnalystPower I have tried the combinations of the Relative dates but it did not work
Hi BItoken ,
You can use a visual level filter. First create a calendar table for slicer:
Date = CALENDAR(MIN('Table'[eff_start_dt]),MAX('Table'[eff_start_dt]))
Then create a visual level filter:
Measure = IF(MAX('Table'[eff_start_dt]) = CALCULATE(MAX('Table'[eff_start_dt]),FILTER(ALL('Table'),'Table'[Account_name] = MAX('Table'[Account_name])&&'Table'[eff_start_dt]<=MAX('Date'[Date]))),1,0)
For more details, please refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
v-deddai1-msft Thanks for your suggestion.
This solution is very close yet so far.
It does not work for all accounts for some reason. it works for some and does not for others.
When i do it in my report, The problem is , it only shows the account name and amount that has data on the selected date for some accounts and does not get the latest record for the selected date.
Your report works though. i created the same measure and applied it in the visual. but for some reason, its not working on mine.
I also made sure that the data type matches on both
Hi BItoken ,
Please confirm that you create a new date table for slicer and it has no relationship with the fact table.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Yes i did create the date table without any relationship to the fact.
Created the same model as u did.