compare data sets based on dates

[AnushreeJoshi]

Hello All,

I am new to power bi and I have to build few metrics based on below sample data set.

I have two give two date filters baseline and comparison and have to display a flag or highlight of the value for the column like status or Priority is changed between those dates.

Suppose I select 30 Oct in base line slicer and 23 Oct in Comparison slicer

a) I have to create vizulization to highlight/flag the columns that have changed.

b) Count of newly added records also details

c) count of deleted records and details

d) count of changed records ( one of the column value is changed) and details

Please help and suggest better implementation for above scenarios.

runid	Id	Status	Priority
30102023	ENG-666	Monitoring	P1
30102023	ENG-111	Monitoring	P1
30102023	ENG-122	Monitoring	P3
30102023	ENG-333	Monitoring	P3
30102023	ENG-555	Teminated	P4
25102023	ENG-555	Teminated	P4
25102023	ENG-111	Monitoring	P1
25102023	ENG-122	Monitoring	P2
25102023	ENG-333	Monitoring	P3
25102023	ENG-666	Monitoring	P4
20102023	ENG-111	Monitoring	P1
20102023	ENG-122	Monitoring	P2
20102023	ENG-333	Monitoring	P3
20102023	ENG-555	Teminated	P4
20102023	ENG-666	Monitoring	P4
15102023	ENG-111	Monitoring	P1
15102023	ENG-122	Monitoring	P2
15102023	ENG-333	Monitoring	P3
15102023	ENG-444	Monitoring	P4
15102023	ENG-555	Monitoring	P4

[v-yangliu-msft · Accepted solution]

Hi  @AnushreeJoshi ,

Here are the steps you can follow：

1. Create calculated table.

Date =
DISTINCT('Table'[runid])

2. Create measure.

Measure_A =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[runid])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[runid])
var _maxID=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[runid]=_maxdate),"ID",'Table'[Id])
return
IF(
    MAX('Table'[runid]) = _mindate&&MAX('Table'[Id])in _maxID&&
    MAX('Table'[Status])<>
    MAXX(FILTER(ALL('Table'),'Table'[runid]=_maxdate&&'Table'[Id]=MAX('Table'[Id])),[Status])
    ,1,0)

Measure_B =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[runid])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[runid])
var _minID=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[runid]=_mindate),"ID",'Table'[Id])
return
IF(
    MAX('Table'[runid]) = _maxdate&&NOT(MAX('Table'[Id]))in _minID,1,0)

Measure_C =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[runid])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[runid])
var _maxID=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[runid]=_maxdate),"ID",'Table'[Id])
return
IF(
    MAX('Table'[runid]) = _mindate&&NOT(MAX('Table'[Id]))in _maxID,1,0)

 3. Place [Measure_A], [Measure_B], [Measure_C] into Visual's Filter and set it to 1.

4. Result:

A and D results in a Visual.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[AnushreeJoshi]

@v-yangliu-msft 

Thank you so much for your solution. I will try to implement it and will let you know.