YoY % variance in a Card visual ?

[LuisNC]

Hello dear Community,

I am pretty new to Power BI.

I need to calculate the YoY % change for the number of orders in a dataset.

The order ID is a column with 3055 unique values, and there are a few years of records in the dataset.

From what I have read and experienced, using a quick measure here doesn't work; it would work if there were 2 years of records in the dataset, but there's 3 o 4.

I mean the formula takes into account the last year, but not the CURRENT year (instead ALL years in the dataset but the last year, I think):

So is there a way to show the YoY % Variance in a Card Visual?

I have prepared a Table visual as an alternative... but I have been asked to do it in a Card preferably.

This is the Table I have prepared using some DAX:

And this is a screenshot of the report, with a circle highlighting the Card I'd like to create:

I hope I have explained myself 😉

I look forward to your help guys, many thanks in advance!

[AlanFredes]

Hi LuisNC,

I see 2 possible solutions:

1.- Use Power BI Time Intelligence. You would need to add Date into the card’s filter pane and set the filter to Relative date. Then set that to the current year. But note this would assume you have 2023 data. If so then the DISTINCTCOUNT() function would recognize you just need the current year.
For previous year you would use the function PREVIOUSYEAR () to get previous year data.

Now since in the example the Max year is 2017 I would recommend building a measure that identifies this and uses that to build the desired output.

2.- The measure would be:

Count of Ortder_ID YoY% =

VAR CURR_YEAR =
                CALCULATE(
                                DISTINCTCOUNT(‘Order’[Order_UD],
                                ‘Date’[Year] = MAX(‘Date’[Year])
                )

VAR PREV_YEAR =

                CALCULATE(
                                DISTINCTCOUNT(‘Order’[Order_UD],
                                ‘Date’[Year] = MAX(‘Date’[Year])-1
                )

RETURN
                DIVIDE((CURR_YEAR – PREV_YEAR),PREV_YEAR)

It is low tech but should work.

Let me know how it goes.

Regards,

[LuisNC]

Hello Alan,

Thanks for your reply.

I must be doing something wrong but it doesn't work.

I have added the filter you mention to the card:

 , and then the code as a measure to the card:

It doesn't seem to be working. There's a typo in the bit of code for "Order_UD", which I changed to "Order_ID", and still doesn't work.

I don't know where is the issue...

[AlanFredes]

Hi Luis,

The two options were not supposed to be applied together.
The DAX formula I sent should be used without the filters added. Also, the table and column names don't seem to be completely correct. My keyboard doesn't write the " ' " correctly so you will need to rewrite the table and column names. Make sure the red underscore gets cleared, that is how you know the syntaxis is correct.

And thanks for catching the order_UD error it is in fact a typo and should be Order_ID.

[LuisNC]

Hello @Alan 

I have tried the formula, and again it doesn't work 😞

Please see a screen shot below:

I have put the formula in a Card to try it. There's no red underline so I guess the formula is OK?

However there's an error message in the Card after adding the measure:

How could I make it work??

Many thanks in advance for your help 🙂

[AlanFredes · Accepted solution]

Hi LuisNC,

I created an example based on a public database (North Wind).

Note, you won't be able to refresh the data since it is pointing to a DB on my computer. But you should be able to open the file and look at how the measures work.

Link to file: https://1drv.ms/u/s!ApozY8vVlQj8hEr9UBKeL7Fh6DTJ?e=uc5QXT

Let me know how it goes.

[LuisNC]

Dude it seems to work now 🙂

And furthermore I think I understand the code lol

Many thanks for your help and your time!!

[AlanFredes]

Thanks, LuisNC I appreciate it.

[AlanFredes]

Would appreciate a Kudos.

[LuisNC]

What is a Kudos?