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.