Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kcantor
Community Champion
Community Champion

Is this if statement possible?

I would like to add a column to my date table either through query editor or adding a column through a measure. In essence I would like for the expression to look at todays official "Day of the Year" (today is day 238). Then I would like for it to look at the other day of the years on the date table and determine if they are less than today's number without regard to year. For example, a day in December of 2015 would get a pass but a day in January of 2015 would get a flag. So, basically in plain language what I want is:
if [dayofyear] is less than 'today's [dayofyear]' then 1 else 0

 

Is this even possible?

@MattAllington

@Greg_Deckler





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
KGrice
Memorable Member
Memorable Member

Hi @kcantor. You can do this in the query editor using the Date.DayOfYear and DateTime.LocalNow functions. Without introducing any helper columns, here it is in one formula:

 

Column = if Date.DayOfYear([Date]) < Date.DayOfYear(DateTime.LocalNow()) then 1 else 0

 

And here's the DAX equivalent if you want to do it there:

 

Column = IF(
(YEARFRAC(DATE(YEAR(TableName[Date]), 1, 1), TableName[Date], 3)*365+1)
< (YEARFRAC(DATE(YEAR(TODAY()), 1, 1), TODAY(), 3)*365+1)
, 1, 0)

 

 

View solution in original post

4 REPLIES 4
KGrice
Memorable Member
Memorable Member

Hi @kcantor. You can do this in the query editor using the Date.DayOfYear and DateTime.LocalNow functions. Without introducing any helper columns, here it is in one formula:

 

Column = if Date.DayOfYear([Date]) < Date.DayOfYear(DateTime.LocalNow()) then 1 else 0

 

And here's the DAX equivalent if you want to do it there:

 

Column = IF(
(YEARFRAC(DATE(YEAR(TableName[Date]), 1, 1), TableName[Date], 3)*365+1)
< (YEARFRAC(DATE(YEAR(TODAY()), 1, 1), TODAY(), 3)*365+1)
, 1, 0)

 

 

kcantor
Community Champion
Community Champion

@KGrice

Do you have any idea what you just did? You created the missing link that so many of us on here have been seeking to auto filter last year to date sales to stop calculating and not autofill when attempting to complete the elusive week over week calculation that many report consumers ask for (the ones who are afraid of the filter and slicer buttons). Well, at least those of us with date tables that continue on to the end of the year and don't want to look at extra data for incomplete weeks. I dropped that in as a report level filter.

That works beautifully and now I can go on vacation without my laptop and without a care in the world.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Nice! I was wondering about the intended purpose, but that makes sense. I just haven't needed it yet. Enjoy your vacation!

kcantor
Community Champion
Community Champion

In case someone else is reading this . . .

I used the Query Editor version. The sticking point for me had been attempting to use DAX language within Query Editor (not for the entire thing, just to determine what today is). I completely forgot to check my choice and defaulted to the DAX version of TODAY().

I have uploaded the model and will be watching it for the next couple of days to make sure it works over time but it seems to be perfect so far.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.