Forum Discussion
compare data sets based on dates
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 |
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
2 Replies
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