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 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 🙂

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

    • 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!