Forum Discussion
Compare data between 2 dates
Hi All,
I have a History tracking table that tracks all changes for a given account and loads all changes with a load date to the table.
Now in Power BI we need to create a comparison of data between 2 different dates.
For example Account 1 has an data for June 9, June 12, June 16th.
If the user wants to see what was the data for June 11th then June 9th record would be the latest date for the slected date (June 11)
If the user wants to see what was the data for June 13th then June 12th record would be the latest date for the slected date (June 13)
If the user wants to see what was the data for June 12th then June 12th record would be the latest date for the slected date (June 12)
The user would be selecting the dates on demand. Any suggestion on how to achieve this scenario?
Thanks in advance!!
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
11 Replies
BItoken , in case you need only 2 dates, not based on each account, you can try like
measure =
var _max1 = MAXX(allselected('Date'), 'Date'[Date])
var _max = maxx(filter(all(Table), Table[Date] <= _max1), Table[Date])
var _min = maxx(filter(all(Table), Table[Date] <= _max), Table[Date])
return
calculate(sum(Table[Value), filter(Table, Table[Date] = _max)) - calculate(sum(Table[Value), filter(Table, Table[Date] = _min ))
or
measure =
var _max1 = MAXX(allselected('Date'), 'Date'[Date])
var _max = maxx(filter(all(Table), Table[Date] <= _max1), Table[Date])
var _min = maxx(filter(all(Table), Table[Date] <= _max), Table[Date])
return
calculate(sum(Table[Value), filter(all(Table[Date]), Table[Date] = _max)) - calculate(sum(Table[Value), filter(all(Table[Date]), Table[Date] = _min ))Hi amitchandak ,
Thanks for the response. But i need to show what the data for each account was on one date and what was the data for each of the account on the other selected date.
amitchandak the challenge is based on the user selection, the table has to display the latest data on or beofre that day.
Try a relative date filter or Date between filter
Both are under slicer
your column is this case is "Period" needs to be a data type of "Date".
Then select the type of slicer that you like best, on the upper down arrow.
A combinaton of slicers may get the job done.
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
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
