topic compare data sets based on dates in DAX Commands and Tips

compare data sets based on dates

Anonymous — Mon, 30 Oct 2023 07:21:14 GMT

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

Re: compare data sets based on dates

Anonymous — Wed, 01 Nov 2023 07:47:36 GMT

Hi Anonymous ,

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

Re: compare data sets based on dates

Anonymous — Wed, 01 Nov 2023 10:50:18 GMT

Anonymous

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