Forum Discussion

BItoken's avatar
BItoken
Helper III
5 years ago
Solved

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 be...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

amitchandak's avatar
amitchandak
Super User
5 years ago

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

  • BItoken's avatar
    BItoken
    Helper III
    5 years ago

    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. 

  • BItoken's avatar
    BItoken
    Helper III
    5 years ago

    amitchandak the challenge is based on the user selection, the table has to display the latest data on or beofre that day.