Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
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