DAX YTD Previous Years based in a different Calendar

[ofeliajesus]

I want to calculate YTD until the last month for the last years for sales by Year and Category, but I have a requisite.

I have a [Dim Date] dimension and an Order table like below.

Date	DateKey	Day of Month	Month Number	Month Name	Year Number	Company Month Num	Company Month	Company Year
27/09/2023 00:00	20230927	27	9	September	2023	9	September	2023
28/09/2023 00:00	20230928	28	9	September	2023	9	September	2023
29/09/2023 00:00	20230929	29	9	September	2023	9	September	2023
30/09/2023 00:00	20230930	30	9	September	2023	9	September	2023
01/10/2023 00:00	20231001	1	10	October	2023	9	September	2023
02/10/2023 00:00	20231002	2	10	October	2023	10	October	2023
03/10/2023 00:00	20231003	3	10	October	2023	10	October	2023
04/10/2023 00:00	20231004	4	10	October	2023	10	October	2023

Sales	OrderDate	Datekey	CategoryName	Year
114	10/06/2022 00:00	20220610	Seafood	2022
52	23/09/2022 00:00	20220923	Condiments	2022
228	01/10/2022 00:00	20221001	Beverages	2022
803	01/11/2022 00:00	20221101	Seafood	2022
155	23/11/2022 00:00	20221123	Beverages	2022
155	03/12/2022 00:00	20221203	Seafood	2022
155	15/12/2022 00:00	20221215	Condiments	2022
52	21/02/2023 00:00	20230221	Seafood	2023
114	22/03/2023 00:00	20230322	Seafood	2023
596	02/05/2023 00:00	20230502	Condiments	2023
155	03/05/2023 00:00	20230503	Beverages	2023
114	04/05/2023 00:00	20230504	Seafood	2023
155	26/08/2023 00:00	20230826	Beverages	2023
114	19/10/2023 00:00	20231019	Condiments	2023

As you can see my  [Dim Date] has Company Month, because some days in the month beginning or ending can belong to a different month so we need to
consider it to calculate the sales amount.
Following the data in my tables I want the following result considering today is October 30th 2023

	2022	2023
Beverages	228	310
Condiments	52	710
Seafoof	114	280

I used the following approach but doesn't work, can you advise me, please?

YTD1 = TOTALYTD([Sales],'Dim Date'[Date])

YTD SPPYs1 = 

var LastMonth = MONTH(today())-1

var LastDateofLastMonth = CALCULATE(Max('Dim Date'[Date]),'Dim Date'[Company Month Num]=LastMonth)

VAR month = MONTH(LastDateofLastMonth )

VAR day = DAY(LastDateofLastMonth )

return

CALCULATE(
    [YTD],
    'Dim Date'[Month Number]<=month&&
    'Dim Date'[Day of Month]<=day)

Thank you

[FreemanZ]

hi @ofeliajesus ,

Not sure if i get you right. Tried to simulate your challenge with data table like:

Date	Name	Sales
1/1/2022	A	1
4/1/2022	A	2
7/1/2022	A	3
10/1/2022	A	4
1/1/2023	A	5
4/1/2023	A	6
7/1/2023	A	7
10/1/2023	A	8
1/1/2024	A	9
1/1/2022	B	10
4/1/2022	B	20
7/1/2022	B	30
10/1/2022	B	40
1/1/2023	B	50
4/1/2023	B	60
7/1/2023	B	70
10/1/2023	B	80
1/1/2024	B	90

and plot a matrix visual with dates[yy/mm] column and a measure like:

LastYearPreviousMonthYTD = 
CALCULATE(
    SUM(data[Sales]),
    DATEADD( SAMEPERIODLASTYEAR( DATESYTD( dates[date])), -1, MONTH)
) +0

it worked like:

For more info, please refer to the file over the cloud. 

[ofeliajesus · Accepted solution]

Hi Freeman,

Thank you for your answer, maybe I did explain it not very clearly.
I solved the problem with the following code

Sales YTD = 
  var todayCompanyMonth = LOOKUPVALUE('Dim Date'[Company Month Num], 'Dim Date'[Date], TODAY())
  var LastcloseCompanyMonth = todayCompanyMonth -1 
  var todayCompanyYear = LOOKUPVALUE('Dim Date'[Company Year], 'Dim Date'[Date], TODAY())
  var selectedCompanyYear = SELECTEDVALUE('Dim Date'[Company Year], todayCompanyYear)

  var result = CALCULATE(SUM('Order'[Sales]), 'Dim Date'[Company Year] = selectedCompanyYear && 'Dim Date'[Company Month Num] < LastcloseCompanyMonth)

  RETURN result