Comparison between last week status and this week status

[H_insight]

Hello,

In my dummy sample dataset, I am trying to compare last week's status and this week's status for each of the selected employees.

For example, when I select 10/01/2022, I would expect to show the status values for the current week (10 to 14/01/2022) and last week (03 to 07/01/2022):

Hope the above ilsturation helps, but if not, happy to answer any questions.

Sample PBIX file.

[H_insight]

I am just thinking loudly here, maybe I should add a calculated column with a lookupvalue to return last week's text/value for [Status] into the table? 

I tried to create the below measure, but with no luck (I could be wrong) ....

Test 2 = 
Var _currentweek = SELECTEDVALUE('Calendar'[Week number])
Var _currentyear = SELECTEDVALUE('Calendar'[Year])
Var _maxweek = CALCULATE(MAX('Calendar'[Week number]),ALL('Calendar'))
Var _filter =
    FILTER(ALL('Calendar'),
        'Calendar'[Week number] = SELECTEDVALUE('Calendar'[Week number]) -1 &&
        'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year]))
RETURN
    CALCULATE (
        DISTINCT ( Data[Status] ),
        FILTER(ALL('Calendar'),
        'Calendar'[Week number] < _currentweek &&
        'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year]))
    )

Any idea's?

[Anonymous · Accepted solution]

HI @H_insight,

Perhaps you can try to use the following measure formulas if helps:

Current Week =
VAR _currentDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Data[Status] ), [Status], "," ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            YEAR ( 'Data'[Date] ) = YEAR ( _currentDate )
                && WEEKNUM ( 'Data'[Date], 2 ) = WEEKNUM ( _currentDate, 2 )
        ),
        VALUES ( 'Data'[EmpID] )
    )

Previous Week =
VAR _currentDate =
    MAX ( 'Calendar'[Date] )
VAR _previousDate =
    _currentDate - WEEKDAY ( _currentDate, 2 )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Data[Status] ), [Status], "," ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            YEAR ( 'Data'[Date] ) = YEAR ( _previousDate )
                && WEEKNUM ( 'Data'[Date], 2 ) = WEEKNUM ( _previousDate, 2 )
        ),
        VALUES ( 'Data'[EmpID] )
    )

If the above does not help, can you please share some dummy data with raw table structure and paste it here with table format? (I can't view the shared link) It should help us clarify the data structure and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[VahidDM]

Hi @H_insight 

Please check this link, I think it would be helpful:

https://www.vahiddm.com/post/weekly-time-intelligence-dax

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[H_insight]

Hi @VahidDM 

Thanks for sharing the link.

I am afraid that I am looking to compare/return "Text" with "Text" and not a scalar number.