Forum Discussion
Anonymous
5 years agoNot applicable
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 10...
- 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
Anonymous
5 years agoNot 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