Current week data compare previous data

[Anonymous]

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

[Anonymous · Accepted 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