cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sean Super Contributor
Super Contributor

Re: Day number of year

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

DoubleJ Member
Member

Re: Day number of year

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

Peterwis Frequent Visitor
Frequent Visitor

Re: Day number of year

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

Peterwis Frequent Visitor
Frequent Visitor

Re: Day number of year

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

Peterwis Frequent Visitor
Frequent Visitor

Re: Day number of year

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

 

(Date.DayOfYear(DateTime.LocalNow())

Community Support Team
Community Support Team

Re: Day number of year

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
Peterwis Frequent Visitor
Frequent Visitor

Re: Day number of year

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

Community Support Team
Community Support Team

Re: Day number of year

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
MarcelBeug Super Contributor
Super Contributor

Re: Day number of year

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

Re: Day number of year

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

 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,660)