Re: How to get YTD versus PY using DAX

francesg · ‎05-01-2023

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 · ‎05-05-2023

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 · ‎05-02-2023

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 · ‎05-04-2023

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 · ‎05-07-2023

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.