topic How to get YTD versus PY using DAX in DAX Commands and Tips

How to get YTD versus PY using DAX

francesg — Mon, 01 May 2023 20:57:38 GMT

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

Re: How to get YTD versus PY using DAX

milanpasschier3 — Tue, 02 May 2023 17:54:34 GMT

Hey man,

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

Best,

Milan

Re: How to get YTD versus PY using DAX

milanpasschier3 — Tue, 02 May 2023 17:58:34 GMT

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

Re: How to get YTD versus PY using DAX

francesg — Thu, 04 May 2023 17:57:13 GMT

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

Re: How to get YTD versus PY using DAX

milanpasschier3 — Fri, 05 May 2023 17:17:38 GMT

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

Re: How to get YTD versus PY using DAX

Avishek07 — Fri, 05 May 2023 22:17:21 GMT

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

Re: How to get YTD versus PY using DAX

Avishek07 — Fri, 05 May 2023 22:20:19 GMT

Can u pls upload the PBIx file in here..

Re: How to get YTD versus PY using DAX

milanpasschier3 — Sun, 07 May 2023 09:43:52 GMT

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.