Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nedpbi
Frequent Visitor

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,

1 ACCEPTED SOLUTION
ExcelMonke
Continued Contributor
Continued Contributor

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🤠

View solution in original post

9 REPLIES 9
RossEdwards
Solution Sage
Solution Sage

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
Frequent Visitor

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
Continued Contributor
Continued Contributor

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. 

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
Continued Contributor
Continued Contributor

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! 

 

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
Continued Contributor
Continued Contributor

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🤠

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

ExcelMonke
Continued Contributor
Continued Contributor

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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors