cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## DAX YTD Previous Years based in a different Calendar

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

1 ACCEPTED SOLUTION
Helper I

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

2 REPLIES 2
Super User

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:

Helper I

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