Calculate sales percentage difference between same time period each year

[av9]

Hi 

I have 3 measures:

1. Sales = SUM(Flows[Sales]) - Total Sales

2. MAX Month = CALCULATE(MAX('Calendar'[MonthNum]), ALL('Calendar'), 'Calendar'[CurMonthOffset] = 0) - Calculates the current month

3. Sales  across prior years = VAR MaxMonth = [MAX Month]

RETURN CALCULATE([Sales], Calendar[MonthNum] <= MaxMonth) - Calculates the sales at the same point in time each year.

How do I calculate the percentage difference for each year in one measure using the 'sales across prior years' measure (column B)?

i.e Percentage Difference between  $38,555,646.34 (2020) and $63,153,245.45 (2019) which I am expecting to be -38.95%.

Year	Sales Across Prior Years	Sales
2020	$38,555,646.34	$38,555,646.34
2019	$63,153,245.45	$333,252,492.39
2018	$48,500,600.91	$400,797,642.48
2017	$30,330,033.59	$252,922,999.13

[Icey · Accepted solution]

Hi @av9 ,

Please try this measure:

Difference =
VAR Pre_Sales_across_prior_years =
    CALCULATE (
        [Sales  across prior years],
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
RETURN
    DIVIDE (
        [Sales  across prior years] - Pre_Sales_across_prior_years,
        [Sales  across prior years]
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[av9]

Didn' quite work but I managed to get it to work using Sameperiod last year thanks

[amitchandak]

You should be able to do like this

divide([Sales],[Sales Across Prior Years])-1

You can use time intelligence and calendar And try formula like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Change % = divide([YTD Sales],[Last YTD Sales])-1

example

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/