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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

If it's monday then display last friday date

Hello, I'm trying to create a query where if it's a monday then set the date to the last friday else previous day. So for example for this week monday was the 14th so I would like Todaycode to show 20200911 instead of 20200913 since it usually just takes the previous day.
I've tried to use If Date.Dayofweek(#"Inserted Merged Column", Column1 = 1) then [custom.1] -3 else #"Inserted Merged Column"{0}[Todaycode] without success. Screenshot 2020-09-16 111557.png

 

 

 

let
    Source = DateTime.Date(DateTime.LocalNow()),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Inserted Year" = Table.AddColumn(#"Converted to Table", "Year", each Date.Year([Column1]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Column1]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Column1])-1, Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Day",{{"Month", type text}, {"Day", type text}, {"Year", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.PadStart([Month],2,"0")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.PadStart([Day],2,"0")),
    Custom1 = #"Added Custom1",
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Year", type text}, {"Month", type text}, {"Day", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Todaycode", each Text.Combine({[Year], [Custom], [Custom.1]}, ""), type text),
    Custom2 = #"Inserted Merged Column"{0}[Todaycode]
in
    Custom2

 

 

I would apreciate the help to solve this, thanks in advance! 

 

2 ACCEPTED SOLUTIONS
Jimmy801
Community Champion
Community Champion

Hello @Anonymous 

 

check out this code. It should give you the answer 🙂

let
    Date = #date(2020,9,15),
    MinusOneDayMondayMinus3Days = if Date.DayOfWeek(Date,0)= 1 then Date.AddDays(Date,-3) else Date.AddDays(Date,-1)
in
    MinusOneDayMondayMinus3Days

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

CNENFRNL
Community Champion
Community Champion

Hi, @Anonymous if those intermediary steps aren't used by some other queries, I'm afraid you overcomplicate the conversion between a date and a string. Pls try

let
    Source = #table({"Date"}, {{#date(2020,9,14)}, {DateTime.Date(DateTime.LocalNow())}}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Date.DayOfWeek([Date]) = 1 then [Date] - #duration(3,0,0,0) else [Date]),
    DateStr = Table.TransformColumns(#"Added Custom", {"Custom", each Date.ToText(_, "yyyyMMdd")})
in
    DateStr

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

@CNENFRNL @Jimmy801 

Thank you guys for the help, both soloutions works wonderfully!

Apreciate it, hopefully one day I'll be better at M.  

CNENFRNL
Community Champion
Community Champion

Hi, @Anonymous if those intermediary steps aren't used by some other queries, I'm afraid you overcomplicate the conversion between a date and a string. Pls try

let
    Source = #table({"Date"}, {{#date(2020,9,14)}, {DateTime.Date(DateTime.LocalNow())}}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Date.DayOfWeek([Date]) = 1 then [Date] - #duration(3,0,0,0) else [Date]),
    DateStr = Table.TransformColumns(#"Added Custom", {"Custom", each Date.ToText(_, "yyyyMMdd")})
in
    DateStr

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Jimmy801
Community Champion
Community Champion

Hello @Anonymous 

 

check out this code. It should give you the answer 🙂

let
    Date = #date(2020,9,15),
    MinusOneDayMondayMinus3Days = if Date.DayOfWeek(Date,0)= 1 then Date.AddDays(Date,-3) else Date.AddDays(Date,-1)
in
    MinusOneDayMondayMinus3Days

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors