Forum Discussion

nedpbi's avatar
nedpbi
Icon for Helper I rankHelper I
2 years ago
Solved

Variance between current year and last year in columns

Hi All,

 

I have a table with a measure sales amt. This table has a relationship with another table dates which has the date dimensions.

 

I would like to show the data in a cross table. How can i do this ? I am struggling with the 3rd column mainly to get the variance between ly and cy. Is there a way to define a column like this which is cy - ly kind of a thing.

 

 CYLYVariance
Sales Amt1009010

 

Thanks,

Need Help
  • ExcelMonke's avatar
    ExcelMonke
    2 years ago

    There probably is a way to do that. However, typically, the "Sales Amt" is calculated per a category of sorts. For example, "Sales Amt", per salesperson; store; region; or even date. Does that make sense? If you truly just want "Sales Amt", you could probably do something along the lines of:

    ADDCOLUMNS (
    SUMMARIZE ( SalesTable, [CY], [LY], [Variance] ),
    "Sales Amt", "Sales Amt"
)

    I would need a better understanding of the data to build that DAX for you.

    Lastly, if my response was helpful, marking it as a solution would be greatly appreciated🤠

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Other respondants have provided some methods but i'll give you something using more inbuilt data functions inside Power BI.

    First create a measure that going to do what you need.  For example TOTALYTD() or SUM()

    You can then place that on your report with the date context needed to make it work.

    Next create a new measure for "Last Year" and use the calculate function with SAMEPERIODLASTYEAR.

    Place the new measure on the same table visual with the same date context.

    Example:

     

    Revenue Sum = SUM('Data'[Revenue])
    Revenue Last Year = CALCULATE(
    [Revenue Sum], 
    SAMEPERIODLASTYEAR(DateTable[Date])
)

     


     

  • ExcelMonke's avatar
    ExcelMonke
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    Hi nedpbi,

    Based on the table above, it looks like the variance result is performing as expected. Is there a different result you were expecting for variance?

  • nedpbi's avatar
    nedpbi
    Icon for Helper I rankHelper I
    2 years ago

    hi ExcelMonke

     

    Thats how I want to show the result but I haven't been able to.

     

    I can split the columns by the year to get the last year and current year, but how do I get the variance in the columns ?

     

    Thanks ! 

    • ExcelMonke's avatar
      ExcelMonke
      Icon for Impactful Individual rankImpactful Individual
      2 years ago

      Ah, I see. Have you tried a new measure with the following DAX:

      Variance = [CY]-[LY]

      this is assuming you have the CY and LY measures calculating the CY and LY sales respectively. 

      • nedpbi's avatar
        nedpbi
        Icon for Helper I rankHelper I
        2 years ago

        hi ExcelMonke 

         

        Sorry I am new to power bi and am missing something obvious.

         

        The sales amt is actually a measure and I want to split this measure by the last year, current year and calculate the variance. I have a column defined as if dates in current year then "CY", else dates in last year "LY". 

         

        Not sure if there is a better way to do this.

         

        Thanks,