DatesYTD Calculate YTD across multiple years

[Anonymous]

I'm trying to calculate YTD values across multiple years and thought I had it until I realized the DATESYTD appears to sum backwards from 12/31 to the date added to the context.  For example

YTDAttyHours = CALCULATE(SUM('Timekeeper Details (Last 5 years)'[Atty Hours]),DATESYTD((Dates[Date]),"11/30"))

the "11/30" actually gives me the sum of each date AFTER 11/30 instead of YTD through 11/30. Can someone help me figure out how to do YTD calculations intead of this backwards calculation?

Thanks!

[KNP]

Hi @Anonymous , 

Please ignore if you need a DAX solution.

In M you could do it like this (or any variation of this).

let
    //***Source data***
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdnLrRtZEARRX95aAJlR/bVFGP/dGBGiCPBmbGN1D6oWja7fv3/yeObBM9fPr588f/779fuHTtNp67R3Ojqdna5Od6c8pcn7I4CIIEKIGCKIiCLCiDgQBzYHcSAOxIE4EAfiQByIY8QxNo/Hk16xNU2nrdPe6eh0dro63Z1eK1ZN3h8BRAQRQsQQQUQUEUbEgTiwOYgDcSAOxIE4EAfmeDyn12RN02nrtHc6Op2drk53p9eaVJP3RwARQYQQMUQQEUWEEXEgDmwO4kAciANxIA7EgTgQx4hjbB6P59YrtqbptHXaOx2dzk5Xp7vTa8WqyfsjgIggQogYIoiIIsKIOBAHNgdxIA7EgTgQB+JAHIhjbB6P597rtKbptHXaOx2dzk5Xp7vTa52qyfsjgIggQogYIoiIIsKIOBAHNgdxIA7EgTgQB+JAHIhjxDE2j8fz6BVb03TaOu2djk5np6vT3em1YtXk/RFARBAhRAwRREQRYUQciAObgzgQB+JAHIgDcSAOxDE2j8fz7HVa03TaOu2djk5np6vT3em1TtXk/RFARBAhRAwRREQRYUQciAObgzgQB+JAHIgDcSAOxDHiGJvH43n1iq1pOm2d9k5Hp7PT1enu9FqxavL+CCAiiBAihggioogwIg7Egc1BHIgDcSAOxIE4EAfiGHGMzePxvHvF1jSdtk57p6PT2enqdHd6rVg1eX8EEBFECBFDBBFRRBgRB+LA5iAOxIE4EAfiQByIA3GMzePxZ0rrOlWaTlunvdPR6ex0dbo7/Qnd5P0RQEQQIUQMEUREEWFEHIgDm4M4EAfiQByIA3EgDsQx4hibxyP9l73SdNo67Z2OTmenq9Pd6bVi8pe9mwAiggghYoggIooII+JAHNgcxIE4EAfiQByIA3EgjrF5PNJ/1CtNp63T3unodHa6Ot2dXuskf9S7CSAiiBAihggioogwIg7Egc1BHIgDcSAOxIE4EAfiGHGMzeN9x7m/vrHWNJ22Tnuno9PZ6ep0d/rcBe+vFesmgIggQogYIoiIIsKIOBAHNgdxIA7EgTgQB+JAHIhjxDE2j/cN6HvF1jSdtk57p6PT2enqdHf63AWXFasmgIggQogYIoiIIsKIOBAHNgdxIA7EgTgQB+LAHO87zvearGk6bZ32Tkens9PV6e70uQsua1JNABFBhBAxRBARRYQRcSAObA7iQByIA3EgDsSBOBDHiGNsHu/bzveKrWk6bZ32Tkens9PV6e70uQsuK1ZNABFBhBAxRBARRYQRcSAObA7iQByIA3EgDsSBOBDH2Dzed5zvdVrTdNo67Z2OTmenq9Pd6XMXXNapmgAiggghYoggIooII+JAHNgcxIE4EAfiQByIA3EgjhHH2Dzet53vFVvTdNo67Z2OTmenq9Pd6XMXXFasmgAiggghYoggIooII+JAHNgcxIE4EAfiQByIA3EgjrF5vO843+u0pum0ddo7HZ3OTlenu9PnLrisUzUBRAQRQsQQQUQUEUbEgTiwOYgDcSAOxIE4EAfiQBwjjrF5vO843yu2pum0ddo7HZ3OTlenu9PnLrisWDUBRAQRQsQQQUQUEUbEgTiwOYgDcSAOxIE4EAfiQBwjjrF5vG873yu2pum0ddo7HZ3OTlenu9PnLrisWDUBRAQRQsQQQUQUEUbEgTiwOYgDcSAOxIE4EAfiQBxj83jfcb7WqdJ02jrtnY5OZ6er093pcxf8XqduAogIIoSIIYKIKCKMiANxYHMQB+JAHIgDcSAOxIE4Rhxj83jfdr5XrP+yV9o67Z2OTmenq9Pd6XMXXFZM/rJ3E0GEEDFEEBFFhBFxIA5sDuJAHIgDcSAOxIE4EMfYPN53nO916j/qlbZOe6ej09np6nR3+twFl3WSP+rdRBAhRAwRREQRYUQciAObgzgQB+JAHIgDcSAOxDHiGJvH3zsOz69vrDVNp63T3unodHa6Ot2d/t0Fv5q8PwKICCKEiCGCiCgijIgDcWBzEAfiQByIA3EgDsSBOEYcY/P4ewNaVmxN02nrtHc6Op2drk53p393wXXFqgkgIogQIoYIIqKIMCIOVsd//wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Value", Int64.Type}}),

    //***Added Year for grouping purposes***
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),

    //***Added Day of Year for filtering***
    #"Added Custom" = Table.AddColumn(#"Inserted Year", "Custom", each Date.DayOfYear([Date])),

    //***Sorted to find MAX Day of Year for current year***
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Date", Order.Descending}}),

    //***Drill down to define DayInYear used in filtering***
    DayInYear = #"Sorted Rows"{0}[Custom],

    //***Filter rows to show less than or equal to 'DayInYear'***
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [Custom] <= DayInYear),

    //***Group by Year***
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Year"}, {{"SumValue", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

Hope this helps.

Regards,

Kim

[parry2k]

@Anonymous not sure wht you mean each date after, giving 11/30 , you are telling what your year looks like and in this case it would be Dec 1st to Nov 30th.

So for each year, the ytd range will Dec 1st and Nov 30th and will give you the number. What is the actual issue? can you share sample data dn explain what is not working and what you are expecting?

[Anonymous]

I would just like a simple YTD calculation.  i.e. Jan 1st through the current date.  When I put in "11/30" it gives me 11/30 through the end of the year.  This is the opposite of YTD.

[parry2k]

@Anonymous you don't need to put 11/30 value, no value for 2nd parameter.

Would appreciate Kudos 🙂 if my solution helped.

[Anonymous]

@parry2kBut I'm trying to calculate YTD across Years with Years as Axis labels.  If I don't put it a date, it won't know what month to stop at for previous years, correct?

[parry2k]

@Anonymous it would, you have to try it to test it. and read about DATESYTD function here   Would appreciate Kudos 🙂 if my solution helped.