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]

@Ashish_MathurI have a requirement to add previous quarter's to current quarter's forecasts, throughout the year. Quarter to date was probably not the best way to describe it because yes that refers to the current quarter. In July I'll need to add the Apr through Sep forecasts for example, and I'm thinking that creating an expression with a Is_QTD filter is the way to go. I tried to make that clear in my original post by noting when there should be a "Y" in the Is_QTD flag column and made it more clear below.

[Ashish_Mathur · Accepted solution]

This should ideally not be solved with a calculated column.  It should be solved with a measure.  Share data with budget numbers and show the expected result for a few months.

[Anonymous]

@Ashish_Mathur  Agreed. I figured something out. Thanks!

[Ashish_Mathur]

Hi,

Write this calculated column formula

Is current Q? = 1*(AND(today()>=CALCULATE(MIN(Data[Date]),FILTER(Data,Data[quarter]=EARLIER(Data[quarter]))),today()<=EOMONTH(CALCULATE(MAX(Data[Date]),FILTER(Data,Data[quarter]=EARLIER(Data[quarter]))),0)))

[Anonymous]

Hi @Ashish_Mathur  thank you. I am going to save this for when I need a current quarter flag. I changed my system date to be in July and found that your expression populates 0s for Apr - Jun where I need for them to remain 1s during the month of July along with having 1s in Jul, Aug, and Sep.

[Ashish_Mathur]

You are welcome.  That does not make sense.  If today's quarter is not the current quarter, then there should be a 0 (as very clearly explained by you in your previous post).  1 should appear only for the months of the current quarter.