IF formula using DATE with only MM-DD

[Anders_G]

I'm using an IF formula to determine if TODAY() is within the Current quarter or if it lies in the Past or Future.

The limitation to my formula is that I will have to update the YEAR once a year. I would like to prevent that.

Question : How do I exclude the YEAR variable from this formula? I want it to only look at MONTH and DAY.

Q1 Check = IF ( DATE(2022,4,1) <= TODAY() && DATE(2022,6,30) >= TODAY(), "Current", IF ( DATE(2022,6,30) > TODAY(), "Future", IF ( DATE(2022,4,1) < TODAY(), "Past")))

Thank You!

[Anonymous]

Hi  @Anders_G ,

I created some data:

TableA：

TableB：

Here are the steps you can follow：

1. Create calculated table.

Table =
CALENDAR(
    DATE(2022,1,1),
    DATE(2022,12,31))

Create calculated column.

Qu = QUARTER('Table'[Date])

2. Create measure.

Measure =
var _today=TODAY()
var _Qud=
CALCULATE(MAX('Table'[Qu]),FILTER(ALL('Table'),'Table'[Date]=_today))
var _minQudate=
MINX(FILTER(ALL('Table'),'Table'[Qu]=_Qud),[Date])
var _maxQudate=
MAXX(FILTER(ALL('Table'),'Table'[Qu]=_Qud),[Date])
return
IF(
    _today<_minQudate,
    SUMX(FILTER(ALL(TableA),MONTH('TableA'[Date])=MONTH(_today)),[Amount])
    ,
    SUMX(FILTER(ALL(TableB),MONTH('TableB'[Date])=MONTH(_today)),[Amount]))

3. Result:

If you need pbix, please click here.

IF formula using DATE with only MM-DD.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anders_G]

Hi Liu, thanks for taking your time!

If I understand the Measure correct it is looking at the dates of the two tables  and is picking this month's values from table B, which is 10.

What I want to have is a measure or some other way to look at today's date, compare it with the dates in tables A & B and if the those dates are from a Quarter in the past the values from Table A should be used and if the dates are in the current or future Quarters they should look at Table B. So the table from your pbix should look like in the screenshot below.

[amitchandak]

@Anders_G , You can have a new column like

Qtr flag =

var _today = today()
var _max = eomonth(_today, if( mod(Month(_today),3) =0,0,3-mod(Month(_today),3)))
var _min = eomonth(_max,-3)+1

return

Switch( True() ,

[Date] <_min , "Past" ,

[Date] > _max, "Future",

"Current"

)

[Anders_G]

Thank you. But I'm not sure adding a column will work. 

I have two tables. One for the Past and one for Current and Future. In my dashboard I'm replacing the "Past", "Current" and "Future" text in my example with measures that will sum values depending on the IF formula.

If the formula returns "Past" it uses my measure for Table A. If it returns either "Current" or "Future" it uses the measure for Table B.

would that change the solution?