Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

You are Awesome TheoC 

Just I noticed the following on your solution:

 

The following formula is not working, it doesn't accept to add the end date.

SalesMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATESMTD ( 'Date'[Date] , "09-30" ) )

 How can I incorporate Fiscal Year end in this formula?

 

SalesLstYrMTD = CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Date'[Date] , -1 , MONTH ) )

 

Thanks

TheoC's avatar
TheoC
Community Champion
4 years ago

Hi Anonymous 

 

A pleasure my friend.

 

Okay, for the SalesMTD, you won't need to worry about the Fiscal Year End because it only sums to the MTD.  The same will be applicable for the SalesLstYrMTD so you don't need to worry about the year end period.

 

Let me know if that makes sense?

 

Thanks heaps,

Theo