Creating a quarter to date flag in a date table

[Anonymous]

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!

[Anonymous]

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.

[slorin]

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 

[m_dekorte · Accepted solution]

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!