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 ...
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🤠

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,

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

        No problem! I would recommend building 3 seperate measures: 

        Measure #1: Current Year

         

        CY = TOTALYTD([SALES],'DateTable'[Dates])

         

         This calculates the total sales, year to date. The 'DateTable'[Date] refers to the table you have your dates saved in

         

        Measure #2: Last Year 

         

        LY = CALCULATE([CY],DATEADD(LASTDATE('DateTable'[Date]),-1,YEAR))

         

        This calculates Measure #1, but for the previous year

         

        Measure #3: Variance

         

        Variance = [CY]-[LY]

         

        ---

        Alternatively, you can do this all in a single measure with Variables:

         

        Variance =
VAR _CY = TOTALYTD([SALES],'DateTable'[Dates])
VAR _LY = CALCULATE(_CY,DATEADD(LASTDATE('DateTable'[Date]),-1,YEAR))

RETURN
_CY - _LY

         

        I hope this helps!