Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Creating a quarter to date flag in a date table

Hi everyone,

 

I need to create a quarter to date flag, using DAX or M, in a date table where the fiscal year starts on 4/1. Below are the details around when there would need to be a "Y" within the Is_QTD flag column. Given it's currently June, the flag should show Y for Q1, or Apr - June.

Thanks in advance and kudos to anyone who can assist!

 

 

 

Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    Hi Anonymous 

     

    Give this a go

    let
    CurrentDate = /* #date(2023, 7, 1) */ Date.From( DateTime.FixedLocalNow()),
    Source = Table.FromColumns(
        { List.Transform( {0..15}, each Date.AddMonths( #date(2023, 1, 1), _ )) },
        type table [ Date = date ]
    ),
    AddQTD = Table.AddColumn(Source, "IsQTD", each 
        [   y = if Date.Month(CurrentDate) >3 then 0 else 1, 
            r= ( [Date] >= Date.StartOfQuarter( #date( Date.Year( CurrentDate )-y, 4, 1))) and 
               ( [Date] < Date.EndOfQuarter(CurrentDate))
        ][r], type logical )
in
    AddQTD

     

    with this result

     

    Ps If this helps to solve your query, please mark it as Solution, thanks!

3 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    Hi Anonymous 

     

    Give this a go

    let
    CurrentDate = /* #date(2023, 7, 1) */ Date.From( DateTime.FixedLocalNow()),
    Source = Table.FromColumns(
        { List.Transform( {0..15}, each Date.AddMonths( #date(2023, 1, 1), _ )) },
        type table [ Date = date ]
    ),
    AddQTD = Table.AddColumn(Source, "IsQTD", each 
        [   y = if Date.Month(CurrentDate) >3 then 0 else 1, 
            r= ( [Date] >= Date.StartOfQuarter( #date( Date.Year( CurrentDate )-y, 4, 1))) and 
               ( [Date] < Date.EndOfQuarter(CurrentDate))
        ][r], type logical )
in
    AddQTD

     

    with this result

     

    Ps If this helps to solve your query, please mark it as Solution, thanks!

  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi

    let
    Source = Table.FromColumns({List.Transform({0..11},each Date.AddMonths(#date(2023,4,1),_))}, type table [Month = date]),
    Quarter = Table.AddColumn(Source, "Quarter", each Date.QuarterOfYear(Date.AddMonths([Month],-3)), Int64.Type),
    #"Is QTD" = Table.AddColumn(Quarter, "Is QTD", each [Quarter]<=Date.QuarterOfYear(Date.AddMonths(Date.From(DateTime.LocalNow()),-3)))
    in
    #"Is QTD"

     

    Stéphane 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you slorin  and m_dekorte . Do you know how I'd modfify the code to work with a date table that has a row for each day of every month? In other words; 4/1/23, 4/2/23 and so on.