Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Current week data compare previous data

Hi Power BI export,

every week we got same  structure data in excel

Example:

21-june-2021

IDName
1001A
1002B
1003C

 

14-June-2021

IDName
1001A
1002B
1004X

 

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

IteamCount
WeeK 32
MDD1
FDD1

 

Thanks

Shanvitha

  • Anonymous's avatar
    Anonymous
    5 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

  • Anonymous's avatar
    Anonymous
    Not 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