Forum Discussion
Comparing last year values
Hi
I've a transactional table that contains the sales done for each day.
What I'm trying to achieve is to compare sales by each sales person as follows:
1) YTD sales this year VS last year. So, assume we are now in Oct. So, I want to compare YTD Sales till Oct 2021 compared to YTD sales last year (Jan till Oct 2020).
2) Monthly sales this year compared to last year. E.g. Jan 2020 VS Jan 2021, Feb 2020 VS Feb 2021
3) I want it to be dynamic, so let's stay we start 2022. Then, my current fiscal year will be 2022 and I want to compare in this case YTD Jan 2022 to Jan 2021.
Sampe data below:
| Date | Sales Person | Item Number | Amount ($) | QTY |
| 01-Jan-20 | Sales A | 1001 | $ 200.00 | 3 |
| 01-Jan-20 | Sales B | 1002 | $ 100.00 | 4 |
| 02-Feb-20 | Sales C | 1003 | $ 300.00 | 5 |
| 02-Feb-20 | Sales A | 1004 | $ 500.00 | 4 |
| 03-Mar-20 | Sales B | 1005 | $ 1,000.00 | 7 |
| 03-Mar-20 | Sales C | 2001 | $ 900.00 | 8 |
| 04-Apr-20 | Sales A | 2002 | $ 950.00 | 2 |
| 04-Apr-20 | Sales B | 2003 | $ 302.00 | 4 |
| 01-May-20 | Sales C | 2004 | $ 311.00 | 1 |
| 01-May-20 | Sales A | 2005 | $ 400.00 | 9 |
| 02-Jun-20 | Sales B | 3001 | $ 100.00 | 5 |
| 02-Jun-20 | Sales C | 3002 | $ 200.00 | 6 |
| 03-Jul-20 | Sales A | 3003 | $ 300.00 | 3 |
| 03-Jul-20 | Sales B | 3004 | $ 800.00 | 4 |
| 04-Aug-20 | Sales C | 3005 | $ 900.00 | 5 |
| 04-Aug-20 | Sales A | 4001 | $ 400.00 | 8 |
| 06-Sep-20 | Sales B | 4002 | $ 500.00 | 2 |
| 06-Sep-20 | Sales C | 4003 | $ 600.00 | 4 |
| 01-Oct-20 | Sales A | 4004 | $ 300.00 | 8 |
| 01-Oct-20 | Sales B | 4005 | $ 400.00 | 9 |
| 02-Nov-20 | Sales C | 5001 | $ 100.00 | 4 |
| 02-Nov-20 | Sales A | 5002 | $ 950.00 | 2 |
| 03-Dec-20 | Sales B | 5003 | $ 750.00 | 6 |
| 03-Dec-20 | Sales C | 5004 | $ 650.00 | 5 |
| 01-Jan-21 | Sales C | 1001 | $ 300.00 | 1 |
| 02-Feb-21 | Sales A | 1002 | $ 350.00 | 1 |
| 03-Mar-21 | Sales B | 1003 | $ 450.00 | 3 |
| 04-Apr-21 | Sales C | 1004 | $ 650.00 | 2 |
| 01-May-21 | Sales A | 1005 | $ 300.00 | 4 |
| 02-Jun-21 | Sales B | 2001 | $ 700.00 | 5 |
| 03-Jul-21 | Sales C | 2002 | $ 850.00 | 6 |
| 04-Aug-21 | Sales A | 2003 | $ 830.00 | 7 |
| 06-Sep-21 | Sales B | 2004 | $ 100.00 | 9 |
| 01-Oct-21 | Sales C | 2005 | $ 150.00 | 2 |
Thanks in Advance
Hi Anonymous
You can achieve this by creating the following measures:
1. SalesMTD
SalesMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESMTD ( 'Date'[Date] ) )
2. SalesYTD
SalesYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESYTD ( 'Date'[Date] ) )
3. SalesLstYrMTD
SalesLstYrMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , MONTH ) )
4. SalesLstYTD
SalesLstYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , YEAR ) )
If you haven't already created a Date table, I strongly recommend you create one. Link the 'Date'[Date] field to your 'Table'[Date] field that will automatically generate a one to many relationship.
Date =
VAR MinYear = YEAR ( MIN ( 'Table'[Date] ) )
VAR MaxYear = YEAR ( MAX ( 'Table'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Let me know if you need further assistance!
Hope this helps 🙂
9 Replies
Hi Anonymous
You can achieve this by creating the following measures:
1. SalesMTD
SalesMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESMTD ( 'Date'[Date] ) )
2. SalesYTD
SalesYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESYTD ( 'Date'[Date] ) )
3. SalesLstYrMTD
SalesLstYrMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , MONTH ) )
4. SalesLstYTD
SalesLstYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , YEAR ) )
If you haven't already created a Date table, I strongly recommend you create one. Link the 'Date'[Date] field to your 'Table'[Date] field that will automatically generate a one to many relationship.
Date =
VAR MinYear = YEAR ( MIN ( 'Table'[Date] ) )
VAR MaxYear = YEAR ( MAX ( 'Table'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Let me know if you need further assistance!
Hope this helps 🙂
Thanks a lot TheoC
Qustion. The fiscal year for me starts on 1st Oct and Ends in 30 Sep. How can I use the above in this case? Is there a way to setup start of fiscal year?
Anonymous absolutely. You can adjust the SalesYTD to the following:
SalesYTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESYTD ( 'Date'[Date] , "09-30" ) )
and replicate the same for the others 🙂