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

Hi TheoC  it makes sense but not working for some reason 😞

Here is what I'm facing.

For this measure:

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

If I use the date table that I created per your advise in the above measure, I get blank when I use the "09-30". BUT if I use the date that is in the original table "Table[Date]", then I get results. But I need to define the end of fiscal year for my results to be right.

 

So, if I can solve this, then I think all will work fine.

 

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.