Forum Discussion

365Sdx's avatar
365Sdx
Frequent Visitor
6 years ago

VAR with multiple values

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 th...
MartynRamsden's avatar
MartynRamsden
Icon for Solution Sage rankSolution Sage
6 years ago

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's avatar
    365Sdx
    Frequent Visitor
    6 years ago

    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's avatar
      MartynRamsden
      Icon for Solution Sage rankSolution Sage
      6 years ago

      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