Comparing last year values

[Anonymous]

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, assume we are now in Oct. So, I want to compare YTD Sales till Oct 2021 compared to YTD sales last year (Jan till Oct 2020).

2) Monthly sales this year compared to last year. E.g. Jan 2020 VS Jan 2021, Feb 2020 VS Feb 2021

3) I want it to be dynamic, so let's stay we start 2022. Then, my current fiscal year will be 2022 and I want to compare in this case YTD Jan 2022 to Jan 2021.

Sampe data below:

Date	Sales Person	Item Number	Amount ($)	QTY
01-Jan-20	Sales A	1001	$    200.00	3
01-Jan-20	Sales B	1002	$    100.00	4
02-Feb-20	Sales C	1003	$    300.00	5
02-Feb-20	Sales A	1004	$    500.00	4
03-Mar-20	Sales B	1005	$ 1,000.00	7
03-Mar-20	Sales C	2001	$    900.00	8
04-Apr-20	Sales A	2002	$    950.00	2
04-Apr-20	Sales B	2003	$    302.00	4
01-May-20	Sales C	2004	$    311.00	1
01-May-20	Sales A	2005	$    400.00	9
02-Jun-20	Sales B	3001	$    100.00	5
02-Jun-20	Sales C	3002	$    200.00	6
03-Jul-20	Sales A	3003	$    300.00	3
03-Jul-20	Sales B	3004	$    800.00	4
04-Aug-20	Sales C	3005	$    900.00	5
04-Aug-20	Sales A	4001	$    400.00	8
06-Sep-20	Sales B	4002	$    500.00	2
06-Sep-20	Sales C	4003	$    600.00	4
01-Oct-20	Sales A	4004	$    300.00	8
01-Oct-20	Sales B	4005	$    400.00	9
02-Nov-20	Sales C	5001	$    100.00	4
02-Nov-20	Sales A	5002	$    950.00	2
03-Dec-20	Sales B	5003	$    750.00	6
03-Dec-20	Sales C	5004	$    650.00	5
01-Jan-21	Sales C	1001	$    300.00	1
02-Feb-21	Sales A	1002	$    350.00	1
03-Mar-21	Sales B	1003	$    450.00	3
04-Apr-21	Sales C	1004	$    650.00	2
01-May-21	Sales A	1005	$    300.00	4
02-Jun-21	Sales B	2001	$    700.00	5
03-Jul-21	Sales C	2002	$    850.00	6
04-Aug-21	Sales A	2003	$    830.00	7
06-Sep-21	Sales B	2004	$    100.00	9
01-Oct-21	Sales C	2005	$    150.00	2

Thanks in Advance

[TheoC · Accepted solution]

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]

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]

@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]

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

[Anonymous]

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]

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]

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]

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

[Anonymous]

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.