Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
Hi @bchager6
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!
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.
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
Hi @bchager6
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!