Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to Solution.
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.
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.
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.
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.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |