Previous YTD with Week Number slicer interaction

[mariahMC]

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!

[samratpbi]

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

[mariahMC]

Thank you, this measure worked as well!

[SamsonTruong · Accepted solution]

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

[mariahMC]

Thank you this worked perfectly. 

[SamsonTruong]

Awesome, glad to hear this worked!