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
Peterwis
Frequent Visitor

Day number of year

Hello Forum,

 

I am looking for the simplest way of creating a measure that just returns day number of the year, ie 2017-04-03 would return 93.

 

Note: Not trying to create a calculated column.

 

thx,

 

Peter

21 REPLIES 21
Greg_Deckler
Super User
Super User

Perhaps something like this:

 

Measure = (YEARFRAC("Jan 1 2017",TODAY())*360)+1

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

thx, this seems to work, how can I make the year to be the current year automatically

thx, this seems to work, how can I make the year to be the current year automatically

Sean
Community Champion
Community Champion

My Measure takes care of this Smiley Happy

Peterwis
Frequent Visitor

Yes, I tried yours first for that reason 🙂 I will expand on what I am trying to achieve. I have a table with monthly targets.

 

Jan (20), Feb (25), March (20), April (50), etc

 

I would like to create a measure that gives me the YTD target per a specific date such as April 5. What is the smartest way of doing that?

 

thx

Sean
Community Champion
Community Champion

@Peterwis

How about this...

Day Of Year Measure = DATEDIFF(DATE(YEAR(TODAY()), 1,1), TODAY(), DAY) + 1

 

2 years later and this helped someone else! Thanks!

Curious - is there a way to do this with month number? Basically, I want the result to be the month number of whatever month that TODAY is in.

 

Thanks!

Peterwis
Frequent Visitor

Thx Sean, this syntax does not seem to work for me

Sean
Community Champion
Community Champion

Day Of Year Measure 2 =  ( YEARFRAC ( DATE ( YEAR ( TODAY() ), 1,1 ),TODAY() )*360 ) + 1 
Peterwis
Frequent Visitor

Thx Sean, cannot get this to work either. What am I missing?

Hi

 

How about this (you might have to replace semicolons with commas)? Not sure whether I understand your requirement..:

 

DayOfYear = 
	DATEDIFF(
		DATE(YEAR(MAX('Calendar'[Date])); 1;1); 
		MAX('Calendar'[Date]); 
		DAY) 
	+ 1

 

DayOfYear.PNG

 

Hope this helps!

JJ

Hi JJ, thx for helping out.

 

I would like to be able to create the measure without any reference to any date in a table, ie what is the number of days in the current year.

 

brgds,

 

Peter

Here is what i use to calculate the day of year in a column, if that is any help.

 

(Date.DayOfYear(DateTime.LocalNow())

Sean
Community Champion
Community Champion

Can you post some sample data and also end result expected?

Peterwis
Frequent Visitor

Table.PNG

 

A table would look like this with monthly targets. What is the YTD target on April 5. that measure would return the TotalYTD for the first 3 months + 5 days worth of April target = 94 (29+29+29+43*(5/30)

 

A simple way to get close is to do (TotalYTD for the targetcolumn*12 (no of months)*daynumberofyear)/monthno), but it will not be exact in any way

Hi @Peterwis,

 

If you want to calculate YTD target, you can refer to below formula:

 

YTD= SUMX(FILTER(ALL(Table),[Date]<=Max([Date])&&Year([Date])=Year(Max([Date]))),[Target])

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin,

 

Thx for your help. Not totally sure I understand your syntax. I have tried applying it to my data but I cannot get it to return anything meaningful so maybe it just does not fit with my data per above.

 

thx anyway

 

Peter

Hi @Peterwis,

 

I think your month column is not the date type, right?
If this is a case, you can add a calculated column to transform it to date type, then write a measure calculated on it.

 

Calculate column to analysis date:

 

Analysis Date = DATEVALUE(RIGHT([Month],LEN([Month])-FIND(" ",[Month])))

Measure to calculate YTD:

 

YTD = SUMX(FILTER(ALL(Record),[Analysis Date]<=Max([Analysis Date])&&Year([Analysis Date])=Year(Max([Analysis Date]))),[Target])

 

 

2.pngCapture.PNG

Regards,

Xiaxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaxin, 

 

My current month is already date typ so no need to create an additional column. Will investigate your proposal and see if it solves my problem. 

 

kind regards, 

 

Peter

 

 

Looking at this topic, I get confused whether we are looking for a DAX or M solution.

 

In case of the latter, this works for me:

 

Query MonthlyTargets:

 

let
    Source = Table.FromColumns({{1..4},{20,25,20,50}},type table[Month = Int64.Type, Target = Int64.Type])
in
    Source

 

Query YTDTargets (returning dates 1/1/2017 thru 30/4/2017, each with the YTD Target):

 

let
    Source = Table.FromColumns({List.Dates(#date(2017,1,1),120,#duration(1,0,0,0))},type table[Date = date]),
    YTDTarget = Table.AddColumn(
                    Source,
                    "YTD Target",
                    (YTD) => List.Sum({0}&Table.SelectRows(MonthlyTargets, each [Month] < Date.Month(YTD[Date]))[Target]) +
                             Date.Day(YTD[Date])/Date.Day(Date.EndOfMonth(YTD[Date])) *
                             Table.SelectRows(MonthlyTargets, each [Month] = Date.Month(YTD[Date]))[Target]{0},
                    type number)
in
    YTDTarget

 

Specializing in Power Query Formula Language (M)

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.