Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Level Of Detail Expression

Does anyone know how to implement the formula of LOD in Tableau into Power BI (DAX)?   Formula: if [Last Update Date] = { FIXED : MAX([Last Update Date])} then "Current Week" elseif [Last Update ...
Need Help
Show and Tell
Tips and Tricks
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi Anonymous ,

    Try following:

    base data:

    Measure:

     

    week = 
IF (
    WEEKNUM ( ( MAX ( 'Table'[Date] ) ) ) = WEEKNUM ( TODAY () ),
    "Current Week",
    IF (
        WEEKNUM ( ( MAX ( 'Table'[Date] ) ) )
            = WEEKNUM ( TODAY () ) - 1,
        "Previous Week",
        "null"
    )
)

     

     

    Final get:

     

    And if you not want to get a measure,but rather to new column,use the following dax:

     

    week2 = 
IF (
    WEEKNUM ( (  'Table'[Date]  ) ) = WEEKNUM ( TODAY () ),
    "Current Week",
    IF (
        WEEKNUM ( ( 'Table'[Date]  ) )
            = WEEKNUM ( TODAY () ) - 1,
        "Previous Week",
        "null"
    )
)

     

     

    tip:

    WEEKNUM([date],1) - the first week of the year, 1 means counting from Sunday, 2 means counting from Monday,adjust as you need!

     

    Wish it is helpful for you!

     

    Best Regards

    Lucien

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , if you want get column with this week and last week refer

 

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Type = Switch( True(),
WEEKNUM('Datedim'[entrydate] ) = WEEKNUM( TODAY() ),"This Week" ,
WEEKNUM('Datedim'[entrydate] ) = WEEKNUM( TODAY() )-1,"Last Week" ,
[Week Name]
)

 

 

How work with Fixed LOD in power bi   - https://www.youtube.com/watch?v=QdnOHcZqkPU

 

for WOW comparision

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    This is what i was hoping to get

     

     amitchandak