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.
I have a table like this and year column is availabe with 2020,2019 and 2018.
Dept CY PY
A 100 50
B 40 90
C 30 88
I am able to calculate the Current Year CY using MAX ( Sales Date) but when I try to calculate Previous year the dax
Max(Sale Date), -1, YEAR does not return the correct result. How to get the previous year. I cannot use the time intelligence functions because in the report there is no date column. In the worse case I will use the time column in report and hide it
Thanks
Bishwa
Solved! Go to Solution.
Hi, @BishwaR , did you give a shot to DATEADD(Sales[Date], -1, YEAR)? It's a common way to filter down to previous year; but pls keep in mind that since it's a time intelligence function, results based on an incontiguous date column may vary in contrast to those based on a standard time table.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thank you @CNENFRNL . I think I was not able to make my point clear to you so let me explain my situation.
When I use the dax Max(sales date) and put it in a card I get the following visual.
My question is what dax should I write to get the Previous year which is 2019 similarly in the card.
If I try to do Min( Sales date) I get 2018 but I need 2019. I tried using dateadd but it does not return the Year in the way I need. Thanks
Hi @BishwaR ,
Since there is no date column in your dataset, you use the year as a slicer and create measures like this:
CY =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Year] = SELECTEDVALUE ( 'Table'[Year] )
&& 'Table'[Dept] IN DISTINCT ( 'Table'[Dept] )
)
)
PY =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Year]
= SELECTEDVALUE ( 'Table'[Year] ) - 1
&& 'Table'[Dept] IN DISTINCT ( 'Table'[Dept] )
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |