Historical YTD Aggregation Through Current Period of Each Year

[jyeager]

Good morning! Can any of you nice smart people help me out or point me in the right direction that will help me design a measure to look at previous years-to-date aggregations? Here are the details:

I would like to show year-to-date hours charged for multiple years back but show the historical year through the current period in the year. For example: 

9/18/20: 200 hours

9/18/19: 175 hours

9/18/18: 225 hours ..... etc.

The issue I am encountering is that my previous years-to-date run through 12/31 of each year. Any thoughts?? I have provided my DAX below and an annotated graphic. I used the solution posted in this great post, but while it was giving me last years tot hours, it was still showing through 12/31. Maybe this isnt possible in the way I want to show it but I figured I would reach out to get everyone's throughts. Thanks in advance!!

Here is the measure I have in the graphic below:

hours YTD = CALCULATE(SUM(his_timecard[hours]),DATESYTD(date_table[Date]))

Below is the visual with some annotations:

[v-alq-msft · Accepted solution]

Hi, @jyeager 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create measures as below.

Year to Date = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        [Date]>=DATE(YEAR(TODAY()),1,1)&&
        [Date]<=TODAY()
    )
)

Year to Date previous 1 year = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        [Date]>=DATE(YEAR(TODAY())-1,1,1)&&
        [Date]<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
    )
)

Year to Date previous 2 year = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        [Date]>=DATE(YEAR(TODAY())-2,1,1)&&
        [Date]<=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY()))
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@jyeager 

To get Last Year YTD you need to create another Dax

Last Year Hours =

Calculate (SUM(his_timecard[hours]),DATEADD(Date_Table[Date],-1,Year]))

For Previous to Previous Year the Function will be Calculate (SUM(his_timecard[hours]),DATEADD(Date_Table[Date],-1,Year]))

and then

For LY YTD = Calculate(Last Year Hours,DATESYTD(Date_Table[Date]) 

for LY to LY  = Calculate(Previous to Previous Year Hours,DATESYTD(Date_Table[Date])

Hope this helps ! Please mention this as Solution if you are good with it and Share your  Kudos

Regards

Vijay Perepa

[amitchandak]

@jyeager , Try like

hours YTD =
var _max = maxx(allselected(his_timecard), his_timecard[Date]) // Date from you table check , max date. use correct one
return
if(min(date_table[Date])<_max, CALCULATE(SUM(his_timecard[hours]),DATESYTD(date_table[Date])), blank())