Forum Discussion

romoguy15's avatar
romoguy15
Helper IV
4 years ago
Solved

YTD Total Minus Current Week

Good Morning, I am trying to calculate a YTD total that excludes the current fiscal week I am in because current week is incomplete. For example, we are in week 39. But I only want to see the YTD to...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, romoguy15 ;

    You could try it.

    YTD Total2 = 
var _todayweek=MAXX(FILTER(ALL('Calendar'),[Date]=TODAY()),[Fiscal Week])
return CALCULATE([Total Tickets],FILTER(ALL('Calendar'),[Year]=YEAR(TODAY())&&[Fiscal Week]<_todayweek))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi， romoguy15 ；

    Do you mean the sum of weeks 1-38 in 2020 and weeks 1-38 in 2021? Ps: Now that it's been another week, that's 39 weeks. If so, try the following formula：

    Total Tickets = 
var _todayweek=MAXX(FILTER(ALL('Calendar'),[Date]=TODAY()),[Fiscal Week])
return CALCULATE( COUNT(HVAC[Ticket Id]),FILTER('Calendar',[Fiscal Week]<_todayweek))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu

romoguy15's avatar
romoguy15
Helper IV
4 years ago

v-yalanwu-msft , thank you= so much. That formula worked out perfectly. I was even able to modify it to bring in my LY Total minus one week to give me an equal total this year weeks1-38 and last year weeks1-38.

 

So I was really curious, and you've already helped a ton so no worries if you don't have the time.

Is there a way I can modify the [Total Tickets] measure to basically only sum up the first fiscal week on the calendar to the current completed week(week38) regardless of the year? The reason I want to try and do that is to put the total on the line graph. Currently as you probably noticed 2020 on the line graph goes all the way up to week 52 cause that year is completed but 2021 only goes up to 39. I was going to try and do a custom measure on the [Total Tickets] to only sum up fiscal week 1 all the way through 38 and place it on the line graph.