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.
Hi all,
I've created a dynamic calendar with a Dataflow. It works fine.
I only have one tiny flaw that i do not like too much. Every time intelligence is based on a date field that is created out of a list.
Per default, Sunday is day 0. I want to see Monday as Day 0 (or 1), so all the following time intelligence is working as well as expected.
So i did change everything concerned to day.monday. But I can not achieve it with the Alternate Day Column. If I use Date.IsinCurrentWeek, it will interprete Sunday as part of this logic.
I think I just need to add the day.monday somewhere as I did it e.g. for the "Start of week". That works fine as a parameter for the Current Week logic, but I do not like this work around.
Find the code and examples attached. Thanks for the help.
Try this..
let
StartDate = #date(2023, 12, 1),
EndDate = DateTime.Date(DateTime.LocalNow()),
Source = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}),
ChangedType = Table.TransformColumnTypes(ConvertToTable,{{"Date", type date}}),
AddYear = Table.AddColumn(ChangedType, "Year", each Date.Year([Date]), type number),
AddMonthNumber = Table.AddColumn(AddYear, "Month Number", each Date.Month([Date]), type number),
AddMonthName = Table.AddColumn(AddMonthNumber, "Month Name", each Date.ToText([Date], "MMMM"), type text),
AddQuarter = Table.AddColumn(AddMonthName, "Quarter", each Date.QuarterOfYear([Date]), type number),
AddDayOfWeekNumber = Table.AddColumn(AddQuarter, "Day of Week Number", each Date.DayOfWeek([Date], Day.Monday) + 1, type number),
AddDayOfWeekName = Table.AddColumn(AddDayOfWeekNumber, "Day of Week Name", each Date.ToText([Date], "dddd"), type text),
AddWeekOfYear = Table.AddColumn(AddDayOfWeekName, "Week of Year", each Date.WeekOfYear([Date], Day.Monday), type number),
AddFirstDateOfWeek = Table.AddColumn(AddWeekOfYear, "First Date of Week", each Date.StartOfWeek([Date], Day.Monday), type date)
in
AddFirstDateOfWeek
Proud to be a Super User!
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.