Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
As per my business requirements I have to subtract current year (actual) column values to all the other column(MAR_FCST,PLAN, ACTUAL (2023)) values as shown in the matrix visuals. I am able to that for current year means current year actual column value I am able to subtract from current year mar_fcst, and plan and Actual. But not able to subtract current year actual column value to previous year actual column value.
I am getting correct results in the GTN% GS~
For year 2024 columns.
Explanation is below:
I want to subtract 2024 ACTUAL column value which is 43.8% to other column that is
(43.8-43.8) for plan and (42.8-43.8) for mar-fcst and (42.7-43.8) for plan and (40.9-43.8) for 2023 actual. But I am not able to do that for 2023.
I am using below dax:
please help to guide me how to achieve this.
Thanks in advance for the time and guidance
Hi @ShyamS ,
I can not help you very well by providing DAX syntax and data in the form of screenshots.
For calculations involving this year and last year, it would be better to create a date table with all the dates in the main table.
Please try syntax logic like this:
MEASURE =
VAR _sameDayLastYear =
DATE ( YEAR ( MAX ( 'Date'[Date] ) ) - 1, MONTH ( MAX ( 'Date'[Date] ) ), DAY ( MAX ( 'Date'[Date] ) ) )
VAR _lastYear =
CALCULATE (
SUM ( Table[ACTUAL] ),
FILTER ( ALL ( 'Date' ), 'Date'[Year] = _sameDayLastYear )
)
VAR _actual =
SUM ( Table[ACTUAL] )
RETURN
_lastYear - _actual
please provide me with the pbix file. Remember not to log in to the current account when uploading files to Power Bi Desktop.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |