cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dalemoy Frequent Visitor
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 columnsDATE_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.

 

 PWRBI_capture.JPG

 

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
v-xjiin-msft Super Contributor
Super Contributor

Re: Variances: YoY, monthly and weekly comparision etc

@dalemoy 

 

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

4.PNG

 

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

 

Thanks,
Xi Jin.

View solution in original post

3 REPLIES 3
v-xjiin-msft Super Contributor
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 )
    )
)

22.PNG

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

 

Capture_140218.JPG

 

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_TOTALTotal Sales  Last YearVarianceVariance %
2014£3,269,931.68(blank)(Blank)(Blank)
2015£3,443,951.85£3,269,931.68£174,020.175.32%
2016£3,955,361.91£3,443,951.85£511,410.0614.85%
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
     

 

 

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. 

v-xjiin-msft Super Contributor
Super Contributor

Re: Variances: YoY, monthly and weekly comparision etc

@dalemoy 

 

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

4.PNG

 

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

 

Thanks,
Xi Jin.

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

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

November 2019 Community Highlights

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

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)