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
Boycie92
Resolver I
Resolver I

Power Query Date Table: Current 6 Months

Hi,

 

I am hoping someone can help.

 

I have created a date table in Power Query using the following code:

 

let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional WDStartNum as number) as table =>

      let

    FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 1,

    CurrentDate = Date.From(DateTime.FixedLocalNow()),

    WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,

    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,

    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

    AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,

    TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),

    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),

 

 

    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),

    InsertMonth = Table.AddColumn(InsertYear, "MonthOfYear", each Date.Month([Date]), type number),

    InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Text.Proper( Date.ToText([Date], "MMMM")), type text),

    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month & Year", each [Month Name] & " " & Number.ToText([Year]), type text),

    InsertDayWeek = Table.AddColumn(InsertCalendarMonth, "DayOfWeek", each Date.DayOfWeek([Date]) + WDStart, Int64.Type),

    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Text.Proper( Date.ToText([Date], "dddd" )), type text),

    InsertMonthnYear = Table.AddColumn(InsertDayName , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),

 

 

 InsertWeekNumber= Table.AddColumn(InsertMonthnYear, "ISO Weeknumber", each

      if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0

      then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)

      else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))

      then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),

    InsertISOyear = Table.AddColumn(InsertWeekNumber, "ISO Year", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )),  Int64.Type),

 

 

    InsertWeeknYear = Table.AddColumn(InsertISOyear, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100,  Int64.Type),

    AddFY = Table.AddColumn(InsertWeeknYear, "Fiscal Year", each "FY" & (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), type text),

   

   

    CurrentDateRecord = Table.SelectRows(AddFY, each ([Date] = CurrentDate)),

    CurrentDay = CurrentDateRecord{0}[Date],

    CurrentWeek = CurrentDateRecord{0}[WeeknYear],

    CurrentMonth = CurrentDateRecord{0}[MonthnYear],

    CurrentFY = CurrentDateRecord{0}[Fiscal Year],

    CurrentYear = CurrentDateRecord{0}[Year],

   

   

 

    InsertCurrentFY = Table.AddColumn(AddFY, "IsCurrentFY", each if [Fiscal Year] = CurrentFY then true else false, type logical),

    InsertCurrentMonth = Table.AddColumn(InsertCurrentFY, "IsCurrentMonth", each if [MonthnYear] = CurrentMonth then true else false, type logical),

    InsertCurrentWeek = Table.AddColumn(InsertCurrentMonth, "IsCurrentWeek", each if [WeeknYear] = CurrentWeek then true else false, type logical),

    InsertCurrentDay = Table.AddColumn(InsertCurrentWeek, "IsCurrentDay", each if [Date] = CurrentDay then true else false, type logical)

 

 

 

 

    in

    InsertCurrentDay, documentation = [

    Documentation.Name =  "",

    Documentation.Description = " ",

    Documentation.Category = "",

    Documentation.Version = "",

    Documentation.Author = ""]

    in

  Value.ReplaceType(fnDateTable, Value.ReplaceMetadata(Value.Type(fnDateTable), documentation))

 

 

However, I would like to add another field that identifies if the date record is within the current 6 months.

 

This should be dynamic and adjust to the day/date you look at the data.

 

The 6-month period should be based on financial years.

 

From April-September

And October to March.

 

How I want it to work:

 

If I look at the date table today (18/8/2021)

 

And I had a date record. For example, on the 1/10/2021 then that would be flagged as 0 (as it is not currently within the defined period (April-September). If I had a record with a date of the 1/4/2021 this would flag as 1 as it was in the defined period.

 

However, if I look at the date table on the 31/12/2021. I would expect the record that was flagged as 0 (1/10/2021) to now show as 1 and the record  with a date of the 1/4/2021 to show as 0.

 

Does anyone know how to achieve this?

 

Thanks,

Boycie92

6 REPLIES 6
lbendlin
Super User
Super User

"This should be dynamic and adjust to the day/date you look at the data"

 

That means you would need a measure

 

Measure = TODAY()
 
It also means that you cannot create a flag based on this. Measures cannot impact columns. They can only impact other measures. 
 
Consequently you would need to implement your entire logic through measures.  Possible, but rather inefficient.
 
If you let go of your first requirement and allow this to be a calculated column (only refreshed when dataset is updated) then everything would become much easier.

 

HI @lbendlin 

 

I have somthing simialr built into the M code:     CurrentDateRecord = Table.SelectRows(AddFY, each ([Date] = CurrentDate))

 

Would that not work?

 

If it has to be a caculated column then I would be ok with that.

 

what formual would I need to use for it?

 

Thanks,

Boycie92

Doing it in M code is the same (sort of) as doing it in a calculated column. It is only computed once during the dataset refresh.  If you do not refresh your dataset frequently then the meaning of "CurrentDate"  will get skewed as you move on from the date when the dataset was last refreshed.

 

So yes, your approach would work, but it would not be dynamic.

Hi@lbendlin,

 

 My dataset would be getting refreshed everyday. So I am not concenred about that. I am just confused as to how I can build that flag in and get it to work the way I want.

 

Thanks,

Boycie92

When does your fiscal year start, in April or October?

Hi @lbendlin 

 

It starts in April.

 

Thanks,

Boycie92

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.