Variance between current year and last year in columns

[nedpbi]

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.

	CY	LY	Variance
Sales Amt	100	90	10

Thanks,

[RossEdwards]

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])
)

[nedpbi]

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]

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]

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]

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]

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 · Accepted solution]

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]

Thanks @ExcelMonke, this pointed me in the right direction !

[ExcelMonke]

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?