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🤠

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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