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 total for weeks 1 - 38 of this year.

The measure I tried but was unsuccessful with is below. I need to identify the current week but exclude it from the YTD total.

 

YTD Total -1Week or current week =

VAR CurrentWeek = ALLSELECTED('Calendar'[Fiscal Week])

RETURN

CALCULATE([YTD Total], 'Calendar'[Fiscal Week] - CurrentWeek)

 

I have my YTD Total

YTD Total =

VAR CurrentYear = YEAR(TODAY())
RETURN
CALCULATE([Total Tickets], 'Calendar', 'Calendar'[Year] = CurrentYear)

And my LY Total

LY Total =

VAR CurrentYear = YEAR(TODAY())
RETURN
CALCULATE([Total Tickets], 'Calendar', 'Calendar'[Year] = CurrentYear -1)

 

Thank you in advance for the assistance

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

8 Replies

  • romoguy15's avatar
    romoguy15
    Helper IV
    4 years ago

    Hi YukiK that is great right up but I was not able to put to use some of the mentioned measures but none are working. Most come up blank. For example. This one below

     

    YTD to last week =
    CALCULATE([YTD Total],
    FILTER(
    ALL('Calendar'),
    'Calendar'[Fiscal Week] = WEEKNUM(TODAY()) -1)
    )
     
    I worked on uploading a sample file if this can help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, romoguy15 ;

    If you want to identify the current week but exclude it from the YTD total.You could try to modify the measure .

    YTD Total -1Week or current week = [YTD Total]-CALCULATE([Total Tickets],FILTER('Calendar',WEEKNUM([Date])=WEEKNUM(TODAY())))

    The final output is shown below:

     

    If not right , could you share simple file after removing sensitive information about your data and the output what you want ?
    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.

  • romoguy15's avatar
    romoguy15
    Helper IV
    4 years ago

    Hi v-yalanwu-msft , thank you for chimming in. I do have a sample data set and any sensitive info should all be removed. I had to upload the sample on my drive

    https://community.powerbi.com/t5/Desktop/YTD-till-current-week-in-DAX/m-p/866077

     

    Unfortunately that formula does not give me the total minus current week I am looking for. Below is a YTD total if I manually filtered out the correct fiscal weeks and beside it is the formula I tried. It needs to be 26086 if we were to exclude the current fiscal week.

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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.

  • 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. 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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