Returning a Percentage Difference from Two Cards

[djs1984]

Good Day,

I have a Power BI report that shows two cards; Sales last year, and Sales this year. Let's say these cards are

This Year: 376

Last Year: 300

The result is just the sum of sales, with a filter on the year.

I want to create another card that displays the variance between these two, but that would be calculating on the basis of other calculations, and I'm not sure how to do it.

Maybe it's simple, maybe not. I'm kind of stuck...

[ajohnso2 · Accepted solution]

Hey,

Hopefully you already have your sales for TY and LY as seperate measures in this case youcan just create a new measure to calculate the YoY by simply Sales TY - Sales LY.

In very basic form

Sales TY =
VAR _CurrentYear = MAX('Calendar'[Year]) ** or _CurrentYear = SELECTEDVALUE('Calendar'[Year]) if your using a slicer**

RETURN
CALCULATE(SUM('Fact'[Sales]), ALL('Calendar'[Year]), 'Calendar'[Year] = _CurrentYear)

Sales LY =
VAR _PreviousYear = MAX('Calendar'[Year]) -1 //Previous Year

RETURN
CALCULATE(SUM('Fact'[Sales]), ALL('Calendar'[Year]), 'Calendar'[Year] = _PreviousYear)

Sales YoY =
[Sales TY] - [Sales LY]

Sales YoY % =
DIVIDE(([Sales TY] - [Sales LY]), [Sales LY], 0)

[Bibiano_Geraldo]

Hi @djs1984 ,

Assuming you already have the following measures:

• Sales This Year 
• Sales Last Year 

Create a new measure with this DAX:

Sales Variance = 
VAR SalesThisYear = [Sales This Year]
VAR SalesLastYear = [Sales Last Year]
VAR Variance = SalesThisYear - SalesLastYear
VAR VariancePercentage = 
    IF(
        SalesLastYear <> 0, 
        DIVIDE(Variance, SalesLastYear, 0), 
        BLANK()
    )
RETURN 
VariancePercentage

[Anonymous]

Yes.

You can incorporate the filter into the measure.
( As is done in both suggested solutions by me and @ajohnso2  )
 
See the suggested solutions. The filter is included in the measures by using the "CALCULATE"-function:

CALCULATE(expression , filter )

CALCULATE evaluates an expression, filtered on the second argument.

The filter applied to the cards can be removed when they are in the measures.

[ajohnso2]

Its not ideal having the filter on the visuals as this is more than likely a static value (you will need to constantly update the filter manually). You wont be able to show your variance in a card with a visual filter as you will need the filter context of both years.

I am not sure of your model structure so I can only say to follow standard guidelines.

- Follow a star schema

- Have a calendar dimension

- Have you Fact data

- Create the relevant relationships

If you are able to share your model we can advise the best route

[Anonymous]

Hi.

You can use this method:

- Create a measures for each of your two cards
- Create a new measure to calculate difference using the two measures.
- Add each of the three measures to a card

Eg.

Sales This Year =
    Calculate(
    SUM('Sales'[Amount]),
    YEAR('Dim calender'[Date]) = YEAR (TODAY () )
    
)

, and

Sales Prev Year =
    Calculate(
    SUM('Sales'[Amount]),
    YEAR('Dim calender'[Date]) = YEAR (TODAY () ) -1
    
)

, and the variance as:

Difference =
[Sales Prev Year] - [Sales this year]

It should work, replace the tables references to relevant tables in your data model.

If this solved your problem, consider mark answer as a slolution so others can find solution fast.

Kind regards
Cosm

[ajohnso2 · Accepted solution]

Hey,

Hopefully you already have your sales for TY and LY as seperate measures in this case youcan just create a new measure to calculate the YoY by simply Sales TY - Sales LY.

In very basic form

Sales TY =
VAR _CurrentYear = MAX('Calendar'[Year]) ** or _CurrentYear = SELECTEDVALUE('Calendar'[Year]) if your using a slicer**

RETURN
CALCULATE(SUM('Fact'[Sales]), ALL('Calendar'[Year]), 'Calendar'[Year] = _CurrentYear)

Sales LY =
VAR _PreviousYear = MAX('Calendar'[Year]) -1 //Previous Year

RETURN
CALCULATE(SUM('Fact'[Sales]), ALL('Calendar'[Year]), 'Calendar'[Year] = _PreviousYear)

Sales YoY =
[Sales TY] - [Sales LY]

Sales YoY % =
DIVIDE(([Sales TY] - [Sales LY]), [Sales LY], 0)

[djs1984]

I have one measure - Sales Total, and the date is just a filter applied to each visual, it's not in the measure. Would I need to have the filter in the measure?

[Anonymous · Accepted solution]

Thanks for the reply from Bibiano_Geraldo,cosm and ajohnso2 , please allow me to provide another insight:

Hi, @djs1984 

The solutions they have proposed are excellent. If you find them helpful, please accept their responses as solutions. This will aid the development of our community and help other members with similar issues find solutions more quickly.

To facilitate your understanding of their ideas, I have created the following example:

Last Year = 
CALCULATE(
    SUM('Table'[values]),
    YEAR('Table'[Date]) = YEAR(TODAY())-1
)

This Year = 
CALCULATE(
    SUM('Table'[values]),
    YEAR('Table'[Date]) = YEAR(TODAY())
)

Sales Variance = 
VAR SalesThisYear = [This Year]
VAR SalesLastYear = [Last Year]
VAR Variance1 = SalesThisYear - SalesLastYear
   
RETURN 
IF(
        SalesLastYear <> 0, 
       FORMAT ( DIVIDE(Variance1, SalesLastYear, 0), "0.00%"),
        BLANK()
    )

Here's the final result, which I hope meets your requirements.

Secondly, regarding whether you need to continue using filters, if your card visuals are based on filters, we recommend replacing them with the measures I used. This approach is more convenient, and you can also add the ALL() and ALLSELECTED() functions to the measures to ensure they are affected by the filters.

Last Year1 = 
CALCULATE(
    SUM('Table'[values]),
   FILTER(ALL('Table'), YEAR('Table'[Date]) = YEAR(TODAY())-1)
)

For further details, please refer to:

Power BI DAX: ALL vs. ALLSelected - RADACAD

ALLSELECTED function (DAX) - DAX | Microsoft Learn
ALL function (DAX) - DAX | Microsoft Learn
 

Please find the attached pbix relevant to the case.

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

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