Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BItoken
Helper III
Helper III

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?

 

BItoken_0-1624572895297.png

 

 

Thanks in advance!!

 

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

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

View solution in original post

11 REPLIES 11
v-deddai1-msft
Community Support
Community Support

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

AnalystPower
Helper I
Helper I

Try a relative date filter or Date between filter

Both are under slicer 

AnalystPower_0-1624473683190.png

your column is this case is "Period" needs to be a data type of "Date".

AnalystPower_1-1624473783574.png

 

Then select the type of slicer that you like best, on the upper down arrow.

 

AnalystPower_2-1624473938977.png

A combinaton of slicers may get the job done.

AnalystPower_3-1624474019785.png

 

 

 

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)

 

Capture40.PNG

 

Capture41.PNG

 

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.

 

Capture42.PNG

 

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.

Hi @BItoken ,

 

Did you use the date column from the new table as date slicer? It's better that you can show me your pbix file for troubleshooting. It's quite difficult for me to tell you where you did wrong without your pbix file since it works well in my test pbix.

 

Best Regards,

Dedmon Dai

amitchandak
Super User
Super User

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

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

 

BItoken_1-1624572863088.png

 

 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.