Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dalemoy
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

@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
Solution Sage
Solution Sage

@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.

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. 

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.