How to get YTD versus PY using DAX

[francesg]

Hi, 

I can't seem to find a proper DAX logic that would give me the answer to my YTD and PY (past year).

I have tried the functions e,g, SAMEPERIODLASTYEAR, DATESYTD etc., but it does not appear.

Overview:  my data source is Excel, uploaded on SharePoint. The Date column set in Excel is General/Text. I tried changing the Date column in Power Query to the "Date" data type.

My problem, I could not get the correct YTD for the past year.

Here is my sample data:

In the year 2023, my YTD should be: 4500

Date	Amount in $
20230101	1000
20230201	1500
20230301	2000
	4500

In the year 2022, my YTD PY should be: 3500

Date	Amount
20220101	1000
20220201	1200
20220301	1300
20220401	1400
20220501	1500
20220601	1600
	8,000

How will I get the following:

1. total YTD 2023

2. tota YTD for the Past year 2022

3. month to month for March 2023 and March 2022

I really appreciate anyone's help.

Thank you

Fran

[Avishek07]

u can do something like 

_PreviousYear =  CALCULATE ( [Sales], 'DATE TABLE'[Year] = SELECTEDVALUE ( 'DATE TABLE'[Year] ) - 1 )

OR you can just use you can make use of DATE ADD function after finding Max Month of Max Year

[milanpasschier3]

For example,

month to month change for March 2023 and March 2022 is calculated by:

Month-to-Month Change March = 

VAR SalesMarch2022 = CALCULATE(
    MAX(Data[Amount in $]),
    YEAR(Data[Date]) == 2022 &&
    MONTH(Data[Date]) == 3
)

VAR SalesMarch2023 = CALCULATE(
    MAX(Data[Amount in $]),
    YEAR(Data[Date]) == 2023 &&
    MONTH(Data[Date]) == 3
)

VAR Change = DIVIDE(SalesMarch2023 - SalesMarch2022, SalesMarch2022) 

RETURN

Change

[francesg]

Hi @milanpasschier3 

Thank you so much for the time to look at my issue.

Is there a way we can dynamically get the lastest month from the data source? then match it to the previous month?

example:

Date	Amount
20230101	25
20230201	30
20230301	15
20220101	10
20220201	12
20220301	36

then we will need to get the 

March 2023 = 15

March 2022 = 36

just in case you have time. 🙂  appreciate it

this is still a problem for me

[milanpasschier3]

Hey, you can do:

DynamicMatch = 

VAR LatestMonth = MONTH(MAX(Sheet1[DATE NEW]))
VAR LatestYear = YEAR(MAX(Sheet1[DATE NEW]))

VAR ThisYearSum = CALCULATE(SUM(Sheet1[Amount]), FILTER(Sheet1, YEAR(Sheet1[DATE NEW]) == LatestYear && MONTH(Sheet1[DATE NEW]) == LatestMonth))
VAR PreviousYearSum = CALCULATE(SUM(Sheet1[Amount]), SAMEPERIODLASTYEAR(DATESYTD(Sheet1[DATE NEW])), MONTH(Sheet1[DATE NEW]) = LatestMonth)

VAR Change = DIVIDE(ThisYearSum - PreviousYearSum, PreviousYearSum) 

RETURN

Change

Check: https://drive.google.com/file/d/18X8tmA5X3FFs-1SWSX5hYpJi5dafJzC1/view?usp=sharing

 Best,

Milan

PS. Thumbs up and mark as solution if it fits your needs.

[milanpasschier3]

Hey man, I will look into this tomorrow! Best, Milan

[milanpasschier3]

Hey man,

I fixed it, see: https://drive.google.com/drive/folders/1T-yiO_CxHG9CkZR0upIRb9j-E-WlufAi?usp=sharing

Best,

Milan

[Avishek07]

Can u pls upload the PBIx file in here..