Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

LOD Dates compare previous week to current week

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...
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

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
 
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you!