Forum Discussion

LuisNC's avatar
LuisNC
Icon for Helper I rankHelper I
3 years ago
Solved

YoY % variance in a Card visual ?

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!

 

Need Help

9 Replies

  • AlanFredes's avatar
    AlanFredes
    Icon for Resolver IV rankResolver IV
    3 years ago

    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's avatar
      LuisNC
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        AlanFredes
        Icon for Resolver IV rankResolver IV
        3 years ago

        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.