Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 Date] = { FIXED : MAX(if [Last Update Date] <{ FIXED : MAX([Last Update Date])} then [Last Update Date] end)} then "Previous Week"
END

 

Thanks,

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

Try following:

base data:

vluwangmsft_0-1624329470944.png

Measure:

 

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

 

 

Final get:

vluwangmsft_1-1624329577997.png

 

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

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

Try following:

base data:

vluwangmsft_0-1624329470944.png

Measure:

 

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

 

 

Final get:

vluwangmsft_1-1624329577997.png

 

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
Super User
Super User

@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
Not applicable

This is what i was hoping to get

 

Laedays_0-1623932265915.png

@amitchandak 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.