cancel
Showing results for
Did you mean:
Frequent Visitor

## Variances: YoY, monthly and weekly comparision etc

Hi All,

This is my second post in regards to this particular issue i'm experiencing.

I have a SQL dataset which looks like the following ( each line is an invoice):

 ITEM_TOTAL other columns DATE_ISSUED £      99,828.40 ~~~~~~~~ 14/11/2017 £      55,990.00 ~~~~~~~~ 29/01/2016 £      39,980.00 ~~~~~~~~ 07/12/2016 £      38,328.00 ~~~~~~~~ 31/05/2017 £      35,000.00 ~~~~~~~~ 27/04/2016 £      34,380.00 ~~~~~~~~ 20/04/2016 £      34,228.00 ~~~~~~~~ 16/06/2017 £      31,771.27 ~~~~~~~~ 04/07/2014 £      30,805.20 ~~~~~~~~ 28/04/2016 £      30,000.00 ~~~~~~~~ 21/06/2017 £      29,996.65 ~~~~~~~~ 05/06/2015 £      29,995.00 ~~~~~~~~ 08/08/2014 £      29,995.00 ~~~~~~~~ 08/08/2014 £      29,995.00 ~~~~~~~~ 18/11/2014 £      29,000.00 ~~~~~~~~ 15/12/2016 £      28,495.00 ~~~~~~~~ 14/03/2014 £      27,995.00 ~~~~~~~~ 30/06/2014 £      27,225.00 ~~~~~~~~ 15/11/2017 >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>>

I also have a Date Table which has a relationship to the DATE_ISSUED column above

I have tried a number of different things and have been pouring through the forums with no success.

at the moment, I am trying this measure:

Total Sales previous Year = CALCULATE(SUM(INVOICE[ITEM_TOTAL]),SAMEPERIODLASTYEAR('Date'[Date].[Date]))

but when I put this into my table, the numbers duplicate (see below) - the data starts from many years before 2016.

Can anybody help? my end goal is to be able to show variance and variance percentage Year-on-Year, month against same month last year, and weekly from the same week last year.

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Variances: YoY, monthly and weekly comparision etc

Try following calculated column.

Total ITEM_TOTAL:

```Total ITEM_TOTAL =
SUMX (
FILTER (
INVOICE,
INVOICE[DATE_ISSUED].[Year] = EARLIER ( INVOICE[DATE_ISSUED].[Year] )
),
INVOICE[ITEM_TOTAL]
)```

Total Sales Last Year:

```Total Sales Last Year =
CALCULATE (
MAX ( INVOICE[Total ITEM_TOTAL] ),
FILTER (
INVOICE,
INVOICE[DATE_ISSUED].[Year]
= EARLIER ( INVOICE[DATE_ISSUED].[Year] ) - 1
)
)```

Variance:

```Variance =
IF (
INVOICE[Total Sales Last Year] <> BLANK (),
INVOICE[Total Sales Last Year] - INVOICE[Total ITEM_TOTAL]
)```

Then for month, quarter. You just need to change INVOICE[DATE_ISSUED].[Year] to .[Month] or .[Quarter].

Thanks,
Xi Jin.

3 REPLIES 3
Super Contributor

## Re: Variances: YoY, monthly and weekly comparision etc

@dalemoy

The SAMEPERIODLASTYEAR() function returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. It means this function will go to find the last year for each date in your calendar table. That's why you got the duplicated number and the data started from many years before 2016.

Since you just want to get the last year values. I think you can try following method:

```Total Sales previous Year =
CALCULATE (
SUM ( INVOICE[ITEM_TOTAL] ),
FILTER (
INVOICE,
YEAR ( INVOICE[DATE_ISSUED] )
= YEAR ( MAX ( INVOICE[DATE_ISSUED] ) - 1 )
)
)```

Same to current year or monthly, weekly.

Thanks,
Xi Jin.

Highlighted
Frequent Visitor

## Re: Variances: YoY, monthly and weekly comparision etc

Hi Xi Jin,

Thank you so much for replying.

Unfortuantely the above doesnt work, my numbers for previous year are off by the millions.

the formula I used was the one you sent me:

Total Sales LY =
CALCULATE (
SUM ( INVOICE[ITEM_TOTAL] ),
FILTER (
INVOICE,
YEAR ( INVOICE[DATE_ISSUED] )
= YEAR ( MAX ( INVOICE[DATE_ISSUED] ) - 1 )
)
)

I used a a column instead of a measure which i think is right.

the results as an example would be like below.

 ITEM_TOTAL Total Sales  Last Year Variance Variance % 2014 £3,269,931.68 (blank) (Blank) (Blank) 2015 £3,443,951.85 £3,269,931.68 £174,020.17 5.32% 2016 £3,955,361.91 £3,443,951.85 £511,410.06 14.85% vvvvvv vvvvvvvvvvvv vvvvvvvvvvv vvvvvvvvvv vvvvvvv

I would need this for monthly, quarterly and weekly for the same periods the year before. (e.g. april 2016 vs april 2017)

I would then have these results filter by a  relative date (this calendar week, this calendar month etc) and then have them on card visuals.

Super Contributor

## Re: Variances: YoY, monthly and weekly comparision etc

Try following calculated column.

Total ITEM_TOTAL:

```Total ITEM_TOTAL =
SUMX (
FILTER (
INVOICE,
INVOICE[DATE_ISSUED].[Year] = EARLIER ( INVOICE[DATE_ISSUED].[Year] )
),
INVOICE[ITEM_TOTAL]
)```

Total Sales Last Year:

```Total Sales Last Year =
CALCULATE (
MAX ( INVOICE[Total ITEM_TOTAL] ),
FILTER (
INVOICE,
INVOICE[DATE_ISSUED].[Year]
= EARLIER ( INVOICE[DATE_ISSUED].[Year] ) - 1
)
)```

Variance:

```Variance =
IF (
INVOICE[Total Sales Last Year] <> BLANK (),
INVOICE[Total Sales Last Year] - INVOICE[Total ITEM_TOTAL]
)```

Then for month, quarter. You just need to change INVOICE[DATE_ISSUED].[Year] to .[Month] or .[Quarter].

Thanks,
Xi Jin.

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)