Search code examples
powerbidax

Measure using PREVIOUSYEAR is returning BLANK on card visual but works on matrix

I'm trying to display previous year [Total Sales] on a card visual. Below is the DAX function that I have written for this.

Total Sales = SUM(
    Ecommerce[Sales]
)

Previous Year Sales = 
CALCULATE(
    [Total Sales],
    PREVIOUSYEAR(
        'Calendar'[Date] //This is the marked Date table
    )
)

I viewed the measure on both card and matrix visual (Please note that there are no filters applied on any visual)

enter image description here

As viewed above, the [Previous Year Sales] measure is working fine on matrix visual, however, I was expecting "$1,770,790" to be displayed on card visual as well, but I get (Blank) value.

Referring to official Microsoft documents, it mentions that this function

Returns a table that contains a column of all dates from the previous year, given the last date in the dates column, in the current context.

Since this says "in the current context", I deduced that matrix visual is providing a context (Start of the Year column), hence the measure works correctly but visual card does not provide a context on its own, hence the measure is returning BLANK.

Now, I have two questions here:

  1. In order to show the scalar value on the visual, I modified the measure to below:
Previous Year Sales = 
VAR max_date = MAX('Calendar'[Date])
VAR year = YEAR(max_date)
RETURN
    CALCULATE(
        [Total Sales],
        'Calendar'[Year] = year - 1
    )

enter image description here

This messes up the matrix visual, however, the card visual shows the correct value. Is the updated code that I have written optimized to calculate the previous year sales?

  1. Why does DATESYTD behave differently than PREVIOUSYEAR (context wise)?

I have another measure that calculates the YTD Sales. Code below:

YTD Sales = CALCULATE(
    [Total Sales],
    DATESYTD(
        'Calendar'[Date]
    )
)

When I apply this measure to card and matrix, below is the result

enter image description here

Referring to official Microsoft document for DATESYTD, this also mentions that

Returns a table that contains a column of the dates for the year to date, in the current context.

Since it says "current context", I'm wondering why is this works on a card visual when PREVIOUSYEAR did not work.

P.S. - Thanks for reading all the way through, I'm new to power BI and any help would be appreciated.

Solution

  • In your matrix, you are providing a date context by adding the Start of Year field from your Calendar table. The Card visual doesn't have this context so it is using all the dates in the Calendar. If you were to select one of the last three rows in your Matrix, you will see your Card visual work due to cross-highlight context.

    Answering your second question

    There is either a bug in PREVIOUSYEAR or the documentation is wrong. It is using the earliest date instead of latest date (as per the documentation). As a result, the Card visual is trying to get the previous year of a year the doesn't existing in your Calendar table hence returning blank.

    DATESYTD does use the latest date hence the difference.

    Here is a cheeky solution for you:

    Previous Year Sales = 
  CALCULATE(
    [Total Sales],
    PREVIOUSYEAR( DATESYTD('Calendar'[Date]) )
  )

    This will work for both your Matrix and Card visual since now PREVIOUSYEAR will use the latest date via DATESYTD.

    Answering your first question

    Your modified measure isn't working in the Matrix visual since as explained in the first paragraph, the Matrix has a date context so you will need to remove this context in your measure for it to look outside that context. You can do this via ALL or REMOVEFILTERS. For example:

    Previous Year Sales = 
  var maxYear = MAX('Calendar'[Year])
  return
    CALCULATE(
      [Total Sales],
      'Calendar'[Year] = maxYear - 1,
      REMOVEFILTERS('Calendar')
    )

    The Time Intelligence functions takes care of removing any existing context on the Date/Calendar table for you.