cancel
Showing results for
Search instead 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.

thanks in advance,

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.

If above method doesn't satisfy your requirement, could you please share us your desired result based on your sample data if possible?

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.

please see below.

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.

## Helpful resources

Announcements

#### New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,472)