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, as...
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 🙂
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 🙂
BTW TheoC , does the above work with DATESMTD ? It gives me syntax error.
Also, how can I use this for the following DAX
SalesLstYrMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , MONTH ) )