Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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
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.
Hey man, I will look into this tomorrow! Best, Milan
Hey man,
I fixed it, see: https://drive.google.com/drive/folders/1T-yiO_CxHG9CkZR0upIRb9j-E-WlufAi?usp=sharing
Best,
Milan
Can u pls upload the PBIx file in here..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |