Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a date field in a large table of data, I want to have an IF statement in an added column where it says Yes if the month (or date) is equal to or less than todays date and No if it isnt. This will then be used for a simple YTD slicer that says Yes or No, where selecting Yes will filter only the dates before todays date - hope that made sense!
I tried this formula: if(MONTH([Posting Date]) < TODAY() then "Yes" else "No"
But I keep getting an error Expression.error: The name MONTH wasn't recognised make sure its spelled correctly
Does anyone know what I'm doing wrong? I thought MONTH() was a DAX function
Sample Data:
Posting Date YTD
1/1/19 Yes
1/2/19 Yes
1/3/19 No
1/4/19 No
Solved! Go to Solution.
Hi @sdas028 ,
We can create a calculated column to make sure the reuslt is correct if there are different years in your fact table.
YTD = var monthyear = YEAR(Table1[Posting date])*100+MONTH(Table1[Posting date]) var _today = YEAR(TODAY())*100+MONTH(TODAY()) return IF(monthyear<=_today,"Yes","No")
For more details, please check the pbix as attached.
Regards,
Frank
Hi @sdas028 ,
We can create a calculated column to make sure the reuslt is correct if there are different years in your fact table.
YTD = var monthyear = YEAR(Table1[Posting date])*100+MONTH(Table1[Posting date]) var _today = YEAR(TODAY())*100+MONTH(TODAY()) return IF(monthyear<=_today,"Yes","No")
For more details, please check the pbix as attached.
Regards,
Frank
It is proabably a misleading error. Month() returns an integer. You are comparing an integer to a date. So DAX is going "If 1 < 2/19/2019 then..." and it doesn't know what to do.
IN addition to that, your PostingDates is returning a table, not a scalar value for MONTHS() to work with. Try this. I used MAX() to coax the date out based on the filter context of the pivot table. My sample table has different lables, but you can see what I've done. START is my date field.
IF ( MONTH ( MAX ( Dates[Start] ) ) < MONTH ( TODAY () ), 1, 0 )
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingNote that @Anonymous and mine do two different things. His will tell you any date older than today. Mine will tell you any month older than today. So Feb 1 is not older than today because month(2/1/2019) is not less than month(today()) (assuming today is any day in the month of Feb 2019.
Not sure which you need. Your original example left some room for interpretation. 🙂
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi Edhans,
Thanks so much for your answer this seems to be what I need, is it possible to have it less than or equal to todays Month?
So that February is still Yes YTD?
Also apologies for the misinterpretation!
Thanks,
Sophie
Hi @sdas028 ,
Did you have a chance to check my way?
Regards,
Frank
Hi Frank,
Yes absolutely! This is actually what I ended up using for the solution so thank you very much! 🙂
Apologies I always forget to go back and close off the request, will do that now
Thanks,
Sophie
Hi @sdas028
if you want to flag the past dates, you can use this formula in your column:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |