Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
ofeliajesus
Helper I
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.

 

 DateDateKey Day of MonthMonth Number Month NameYear NumberCompany Month NumCompany MonthCompany Year 
 27/09/2023 00:00  20230927279September20239September2023
 28/09/2023 00:0020230928289 September20239September2023
 29/09/2023 00:0020230929299 September20239September2023
 30/09/2023 00:0020230930309 September20239September2023
 01/10/2023 00:0020231001110October20239September2023
 02/10/2023 00:0020231002210October202310October2023
 03/10/2023 00:0020231003310October202310October2023
 04/10/2023 00:0020231004410October202310October2023

 

 Sales  OrderDateDatekeyCategoryNameYear
114 10/06/2022 00:00   20220610Seafood2022
52 23/09/2022 00:00   20220923Condiments2022
228 01/10/2022 00:00   20221001Beverages2022
803 01/11/2022 00:00   20221101Seafood2022
155 23/11/2022 00:00   20221123Beverages2022
155 03/12/2022 00:00   20221203Seafood2022
155 15/12/2022 00:00   20221215Condiments2022
52 21/02/2023 00:00   20230221Seafood2023
114 22/03/2023 00:00   20230322Seafood2023
596 02/05/2023 00:00   20230502Condiments2023
155 03/05/2023 00:00   20230503Beverages2023
114 04/05/2023 00:00   20230504Seafood2023
155 26/08/2023 00:00   20230826Beverages2023
114 19/10/2023 00:00   20231019Condiments2023

 

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

 

 20222023
Beverages228310
Condiments52710
Seafoof114280

 

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

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

 

 

 

View solution in original post

2 REPLIES 2
FreemanZ
Super User
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:

FreemanZ_0-1698666331066.png

 

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

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

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.