VAR with multiple values

[365Sdx]

Hi,

I'm trying to get values for the same week last year. I can't seem to use the SAMEPERIODLASTYEAR function as it takes it too literally & pulls in values for the same date last year instead of the week which is less clearly defined.

(e.g. this week started on Mon 11th Nov 2019 but the 'same' week last year started on Mon 12th Nov 2018)

I've got a field for week number so was trying to use the following dax code:

Qty Sold (Prior Yr) = 
	VAR CurrentWeek = SELECTEDVALUE( Ref_Dates[Week of Year] )
	VAR CurrentYear = SELECTEDVALUE( Ref_Dates[Year] )
 
	RETURN
CALCULATE( [Qty Sold], 
    		FILTER( ALL( Ref_Dates ),
			Ref_Dates[Week of Year] = CurrentWeek && Ref_Dates[Year] = CurrentYear - 1)) ​

On the surface it worked, but then noticed it doesn't show a total (screenshot 1) & also that it doesn't show anything at all if I roll up the table (e.g to year in screenshot 2).

screenshot 1

screenshot 2

I'm not that familiar with dax but assuming it's got something to do with using SELECTEDVALUE which can only deal with ONE value & I'm not sure of an alternative to use MULTIPLE values (specifically only the ones the visual is filtered on).

Does anyone know a way I can modify the formula so that it shows roll up values & total values?

Appreciate your help.

Thanks

[MartynRamsden]

Hi @365Sdx 

It's not possible to use Power BI's built in time intelligence functions with week based calendars.

You're right that SELECTEDVALUES can only handle a single value. If there is more than one value in the filter context, it will return BLANK() by default.

As such, you can test your CurrentWeek variable to see if it's value is BLANK() and if so, use a different calculation.

Example:

Qty Sold (Prior Yr) =
VAR CurrentWeek = SELECTEDVALUE ( Ref_Dates[Week of Year] )
VAR CurrentYear = SELECTEDVALUE ( Ref_Dates[Year] )
VAR Result =
    IF (
        NOT ( ISBLANK ( CurrentWeek ) ),
        CALCULATE (
            [Qty Sold],
            FILTER ( 
		ALL ( Ref_Dates ),
                Ref_Dates[Week of Year] = CurrentWeek 
		&& Ref_Dates[Year] = CurrentYear - 1
            )
        ),
        CALCULATE (
            [Qty Sold],
            FILTER (
		ALL ( Ref_Date ),
		Ref_Dates[Year] = CurrentYear - 1
	    )
        )
    )
RETURN
    Result

This should return the total Qty Sold for the previous year against the 'Total' row in the matrix.

Note: if you place a filter on the week, this formula will still calculate the total Qty Sold for the previous year (i.e. it will ignore the filter applied on the week).

Best regards,

Martyn

[365Sdx]

Hi Martyn, thanks for looking at this.

I tried it but it's not quite working as the table will be rolled up most of the time (i.e. not showing the time data) & each row will almost always be for multpled weeks.

Do you think there's something similar to SELECTEDVALUES that can deal with all selected values or a better way of trying to get values for the 'equivalent eek' last year?

Thanks

[MartynRamsden]

Hi @365Sdx 

Sorry that didn't work out for you.

There are a few other ways to tackle this but it's hard to recommend anything without fully understanding your data model.

Are you able to share your pbix? Make sure you remove any sensitive data before you do.

Best regards,

Martyn