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 th...
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!

slorin's avatar
slorin
Icon for Super User rankSuper 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