Forum Discussion
Current week data compare previous data
Hi Power BI export,
every week we got same structure data in excel
Example:
21-june-2021
| ID | Name |
| 1001 | A |
| 1002 | B |
| 1003 | C |
14-June-2021
| ID | Name |
| 1001 | A |
| 1002 | B |
| 1004 | X |
21-june compare to 14-june if record not availbe then MDD
14-june compare 21-june if record not availble then FDD
output lookmlikebelow
| Iteam | Count |
| WeeK 3 | 2 |
| MDD | 1 |
| FDD | 1 |
Thanks
Shanvitha
- Anonymous5 years ago
Hi Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a custom column with the date which the date is get from
2. Append these two queries from different two dates in Power Query Editor
3. Create two measures to get the item and count separately
Item = VAR _mindate = DATE ( 2021, 6, 17 ) VAR _maxdate = DATE ( 2021, 6, 24 ) VAR _tab1 = CALCULATETABLE ( VALUES ( 'New'[ID] ), FILTER ( ALLSELECTED ( 'New' ), 'New'[Date] = _mindate ) ) VAR _tab2 = CALCULATETABLE ( VALUES ( 'New'[ID] ), FILTER ( ALLSELECTED ( 'New' ), 'New'[Date] = _maxdate ) ) VAR _tab3 = EXCEPT ( _tab1, _tab2 ) VAR _tab4 = EXCEPT ( _tab2, _tab1 ) RETURN IF ( SELECTEDVALUE ( 'New'[ID] ) IN _tab3, "MOD", IF ( SELECTEDVALUE ( 'New'[ID] ) IN _tab4, "FDD", BLANK () ) )Count = CALCULATE ( COUNT ( 'New'[ID] ), FILTER ( ALLSELECTED ( 'New'[ID] ), NOT ( ISBLANK ( [Item] ) ) ) )Best Regards
1 Reply
- AnonymousNot applicable
Hi Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a custom column with the date which the date is get from
2. Append these two queries from different two dates in Power Query Editor
3. Create two measures to get the item and count separately
Item = VAR _mindate = DATE ( 2021, 6, 17 ) VAR _maxdate = DATE ( 2021, 6, 24 ) VAR _tab1 = CALCULATETABLE ( VALUES ( 'New'[ID] ), FILTER ( ALLSELECTED ( 'New' ), 'New'[Date] = _mindate ) ) VAR _tab2 = CALCULATETABLE ( VALUES ( 'New'[ID] ), FILTER ( ALLSELECTED ( 'New' ), 'New'[Date] = _maxdate ) ) VAR _tab3 = EXCEPT ( _tab1, _tab2 ) VAR _tab4 = EXCEPT ( _tab2, _tab1 ) RETURN IF ( SELECTEDVALUE ( 'New'[ID] ) IN _tab3, "MOD", IF ( SELECTEDVALUE ( 'New'[ID] ) IN _tab4, "FDD", BLANK () ) )Count = CALCULATE ( COUNT ( 'New'[ID] ), FILTER ( ALLSELECTED ( 'New'[ID] ), NOT ( ISBLANK ( [Item] ) ) ) )Best Regards