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 🙂 

TheoC's avatar
TheoC
Community Champion
4 years ago

Anonymous Apologies, can you chant "09-30" to "9/30" apologies!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Nop, not solving the problem.

 

So here's the behaviour.

1) When I put this measure for YTD in this format:

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

I get 0 as a result.

 

2) When I put this measure for YTD in this format (I use date in transaction table):

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

 I get results correct.

 

Now for last year YTD:

When I use this measure

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

 

I get values, but the sum is wrong. I think it's unaware of the Start and end of quarter. This is why the result is wrong.

 

Just sharing my thoughts.