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

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

Thanks a lot TheoC 

Qustion. The fiscal year for me starts on 1st Oct and Ends in 30 Sep. How can I use the above in this case? Is there a way to setup start of fiscal year?

  • 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

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