Last Year Total Not Working in Card Value When Multiple Dates Selected in Slicer

[Anonymous]

Hi, (pbix attached below)

I've been working on getting same week last year value on a card visuals but I can't seem to figure out why this bug exists. Keep close attention on the red box visual for the issue. I created a sample model: Sample Model

The visuals in the dashboard attached is shown below: 

One date selected, both card visuals work

The last year same week value is retrieved by looking up the week number of the current year and its year, and then subtracting 1 from the year value. Thus if we wanted to get the value for 1/4/2021 , we see the week number is 1 and year is 2021 so we need week number 1 and year 2020 for last year same week. See the illustration and measure with this logic below:

Items Sold 1 Year Prior (Multiple Selection) = 
CALCULATE (
    [Total Items Sold],
    FILTER (
        ALL ( 'Sample Calendar Table' ),
        'Sample Calendar Table'[Year]
            = SELECTEDVALUE ( 'Sample Calendar Table'[Year] ) - 1
            && 'Sample Calendar Table'[Week No in Year]
                IN VALUES('Sample Calendar Table'[Week No in Year])
    )
)

The measure above works with multiple dates too except for when the dates selected are found in two different years:

Two dates selected, only the red card visual works (as expected)Two dates selected in two different years, then both cards do not work

My issue is whether it is possible show the the same week last year values under the condition where the dates selected in the slicer are different. How can I get the totals of the last years for dates 12/28/2020 and 1/4/2021? 

My dashboard: Dashboard 

Thank you for any advice or help!

[parry2k]

@Anonymous I know selectedvalue returns on single value 🙂

you can try this:

MAX ( 'Sample Calendar Table'[Year] ) - 1

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k , thank you for the direction! When I validate the numbers, I get different values than the expected. For dates, 12/28/2020 and 1/4/2021 I get 964 and 899, respectively. Added together they total 1863, but on the card visual it says 1771. Nevertheless, this truly gets me closer to what I'm looking for and I appreciate you help! 😄 

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

I think your problem is that measure for multiple selection always show blank if you select days in different years. It is not a good idea to use the week no and year as separate filters in the calculation when you are calculate the sum of multiple selection. I suggest you to combine them to determind the keyvalue like yearweekno. 

Try this code.

Items Sold 1 Year Prior (Multiple Selection) =
VAR _SELECTVALUE =
    SUMMARIZE (
        'Sample Calendar Table',
        'Sample Calendar Table'[Year],
        'Sample Calendar Table'[Week No in Year]
    )
VAR _ADDCOLUMN =
    ADDCOLUMNS ( _SELECTVALUE, "YEAR-1", [Year] - 1 )
VAR _ADDCOLUMN2 =
    ADDCOLUMNS (
        _ADDCOLUMN,
        "COMBINE", COMBINEVALUES ( " ", [YEAR-1], [Week No in Year] )
    )
VAR _SUMMARIZE =
    SUMMARIZE ( _ADDCOLUMN2, [COMBINE] )
RETURN
    CALCULATE (
        [Total Items Sold],
        FILTER (
            ALL ( 'Sample Calendar Table' ),
            COMBINEVALUES (
                " ",
                'Sample Calendar Table'[Year],
                'Sample Calendar Table'[Week No in Year]
            )
                IN _SUMMARIZE
        )
    )

Result is as below. We get correct total 1863.

Best Regards,
Rico Zhou

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

[parry2k]

@Anonymous reason, selectedvalue only returns values if one value is selected otherwise it returns blank, if you add the following two measures and put it in the card visual, you will get the answer. As you will select the values in the slicer, you will see what selectedvalue function returns

Week Selected = SELECTEDVALUE ( 'Sample Calendar Table'[Week No in Year])

Year Selected = SELECTEDVALUE ( 'Sample Calendar Table'[Year] )

s

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k thanks for the quick reply. I added those visuals and yes I can confirm that the SELECTEDVALUE only works with one value. However, I'm selecting multiple dates in the slicer and I'd like to see multiple week numbers and years filtered in the measure, Items Sold 1 Year Prior (Multiple Selection).

In that measure, I added a way to consider multiple Monday dates by using

IN VALUES('Sample Calendar Table'[Week No in Year])

However, I think the issue comes in the year column because of SELECTEDVALUE(). With two different dates coming from two different years, there is a 2020, 2021 values which appear as BLANK() as expected.

Is there a way to create a VALUES(SELECTEDVALUE ( 'Sample Calendar Table'[Year] ) - 1) type logic to account for multiple years?