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🤠

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! 

 

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

Thanks ExcelMonke that helps ! I get the values correctly that way.

 

But is there a way to show the "Sales Amt" label in the rows in the cross table ?

 

Thanks !

  • ExcelMonke's avatar
    ExcelMonke
    Icon for Impactful Individual rankImpactful Individual
    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

      Thanks ExcelMonke, this pointed me in the right direction !