Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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!
Solved! Go to Solution.
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
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! |
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! |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.