Forum Discussion

mariahMC's avatar
mariahMC
Icon for Helper I rankHelper I
1 year ago
Solved

Previous YTD with Week Number slicer interaction

Hello,

I have a visual that lists the years, WTD totals, YTD totals and YoY% based on which week of the year is selected from a slicer. I have three slicers for Region, Year and Week#. The year slicer does not interact with this visual since it already lists the years.

I was able to calculate the YTD totals based on the Week # selection using this measure:

 
Test Total YTD =
CALCULATE (
    [Case Volume],
    FILTER (
        ALL ( 'Calendar Table'[Week Number] ),
        'Calendar Table'[Week Number] <= SELECTEDVALUE ( 'Calendar Table'[Week Number] )
    )
)
 
Here's my dilemna:
I need to calculate the previous YTD for my YoY%, however when I try to use SAMEPERIOD LAST YEAR or PREVIOUSYEAR in my measure, it does not work.
 
Test Total Prev YTD =
CALCULATE ( [Test Total YTD], PREVIOUSYEAR ( 'Calendar Table'[Date] ) )

 

Any help will be appreciated!

Thanks!

Need Help
  • SamsonTruong's avatar
    SamsonTruong
    1 year ago

    Hi mariahMC ,

    Please try the following DAX for your Total Prev YTD measure and let me know if it achieves your desired result:

    Test Total Prev YTD :=
VAR SelectedWeek = SELECTEDVALUE('Calendar Table'[Week Number])
VAR SelectedYear = SELECTEDVALUE('Calendar Table'[Year])

RETURN
CALCULATE(
    [Case Volume],
    FILTER(
        ALL('Calendar Table'),
        'Calendar Table'[Year] = SelectedYear - 1 &&
        'Calendar Table'[Week Number] <= SelectedWeek
    )
)

     

    If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

    Thanks,

    Samson

     

    Connect with me on LinkedIn

    Check out my Blog

    Going to the European Microsoft Fabric Community Conference? Check out my Session

5 Replies

  • SamsonTruong's avatar
    SamsonTruong
    Icon for Super User rankSuper User
    1 year ago

    Hi mariahMC ,

    Please try the following DAX for your Total Prev YTD measure and let me know if it achieves your desired result:

    Test Total Prev YTD :=
VAR SelectedWeek = SELECTEDVALUE('Calendar Table'[Week Number])
VAR SelectedYear = SELECTEDVALUE('Calendar Table'[Year])

RETURN
CALCULATE(
    [Case Volume],
    FILTER(
        ALL('Calendar Table'),
        'Calendar Table'[Year] = SelectedYear - 1 &&
        'Calendar Table'[Week Number] <= SelectedWeek
    )
)

     

    If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

    Thanks,

    Samson

     

    Connect with me on LinkedIn

    Check out my Blog

    Going to the European Microsoft Fabric Community Conference? Check out my Session

  • samratpbi's avatar
    samratpbi
    Icon for Super User rankSuper User
    1 year ago

    Hi, it will not work since date functions are not for weekly calculations. What you can do is, create another measure like below:

     

    Test Total PYTD =
    CALCULATE (
        [Case Volume],
        ALL ( 'Calendar Table'[Week Number] ),
        'Calendar Table'[Year] = SELECTEDVALUE ( 'Calendar Table'[Year] ) - 1 
        'Calendar Table'[Week Number] <= SELECTEDVALUE ( 'Calendar Table'[Week Number] )
    )
     
    If this helps to resolve your problem, then please mark it as solution, thanks - Samrat