LOD Dates compare previous week to current week

[Anonymous]

Is there a way we can do this in Power BI?

My goal is to have a formula that will always determine the 'Current Week' and the 'Previous Week', the rest will show null values.

Is that possible in Power BI?

[selimovd]

Hey @Anonymous ,

that is possible with a calculated column. You just have to compare the weeknumber of the column to the today's week number.

Try the following formula:

Week =
VAR vCurrentWeek = WEEKNUM( TODAY() )
RETURN
    SWITCH(
        WEEKNUM( myTable[Date] ),
        vCurrentWeek, "Current Week",
        vCurrentWeek - 1, "Previous Week",
        BLANK()
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi, i tried to use the formula you said but it's not giving me the expected output. Please see below:

The logic i need is to get the "current" if it's the latest date, then get the "previous" if it's less than the latest date then the rest will be null.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a calculated column as below:

Week = 
VAR _maxdate =
    CALCULATE ( MAX ( 'Data'[Last Update Date] ), ALL ( 'Data' ) )
VAR _secondmaxdate =
    CALCULATE (
        MAX ( 'Data'[Last Update Date] ),
        FILTER ( 'Data', 'Data'[Last Update Date] < _maxdate )
    )
RETURN
    IF (
        'Data'[Last Update Date] = _maxdate,
        "Current Week",
        IF ( 'Data'[Last Update Date] = _secondmaxdate, "Previous Week", BLANK () )
    )

You can also create a measure as below with similar formula:

Measure = 
VAR _maxdate =
    CALCULATE ( MAX ( 'Data'[Last Update Date] ), ALLSELECTED(  'Data' ) )
VAR _secondmaxdate =
    CALCULATE (
        MAX ( 'Data'[Last Update Date] ),
        FILTER ( ALLSELECTED('Data'), 'Data'[Last Update Date] < _maxdate )
    )
RETURN
    IF (
        SELECTEDVALUE('Data'[Last Update Date]) = _maxdate,
        "Current Week",
        IF (SELECTEDVALUE('Data'[Last Update Date]) = _secondmaxdate, "Previous Week", BLANK () )
    )

Best Regards

[Anonymous]

Thank you!