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 ID Name 1001 A 1002 B 1003 C   14-June-2021 ID Name 1001 A 1002 B 10...
  • 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