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!

m_dekorte's avatar
m_dekorte
Icon for Resident Rockstar rankResident 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!