visual calculation Previous in wrong column

[newtopbitoo]

Please have a look at my matrix.
Columns previous year and diff previous year are made with "Visual calculations"
The value previous year

previous year = PREVIOUS([count],columns,[YEAR]) is in the correct column.
The value diff prev year 

diff prev year = [Marketshare] - PREVIOUS([Marketshare],COLUMNS,[YEAR])

shows it's value under 2023 while it should be under 2024.

What is wrong in my formula

[Anonymous · Accepted solution]

Thanks for Kedar_Pande's concern about this issue.

Hi, @newtopbitoo 

I am glad to help you.

Since you didn't give specific datasets for testing, I assumed some datasets myself:

I don't know if you have to use the Previous function, in my tests I didn't use that function and recreated two Measure:

previous year = 
DIVIDE(
    SUM('Table'[count]),
    CALCULATE(
        SUM('Table'[count]),
        ALLEXCEPT('Table', 'Table'[Year])
    ),
    0
) 

diff prev year = 
VAR _CurrentYear = 2024
VAR _PreviousYear = 2023
VAR _CurrentYearPercentage = 
    CALCULATE(
        [previous year],
        FILTER('Table', 'Table'[Year] = _CurrentYear)
    )
VAR _PreviousYearPercentage = 
    CALCULATE(
        [previous year],
        FILTER('Table', 'Table'[Year] = _PreviousYear)
    )
RETURN
    IF(
        MAX('Table'[Year]) = _PreviousYear,
        0,
        _CurrentYearPercentage - _PreviousYearPercentage
    )

Finally I got the result you need:

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[newtopbitoo]

DataNinja777 answered to this topic but the answer does not show here, he said: 
The issue with your formula for calculating diff prev year arises because the PREVIOUS function evaluates the value in the column corresponding to the previous year but does not inherently adjust where the result should be displayed. To correct this behavior, you need to adjust the calculation or approach to ensure the difference appears in the correct column

iff prev year3 = VAR CurrentMarketShare = [Marketshare]

VAR PreviousMarketShare = PREVIOUS ( [Marketshare], COLUMNS, [YEAR] ) RETURN

 IF ( NOT ( ISBLANK (PreviousMarketShare) ), CurrentMarketShare - PreviousMarketShare, BLANK () )
Unfortunately no values showed up at all.
Instead of ISBLANK (PreviousMarketShare) I tried ISBLANK (Previous year) too but that did not help.
 

[Kedar_Pande]

@newtopbitoo 

Updated DAX for diff prev year

diff prev year = 
VAR CurrentMarketShare = [Marketshare] 
VAR PreviousMarketShare = 
PREVIOUS([Marketshare], COLUMNS, [YEAR])
RETURN
CurrentMarketShare - PreviousMarketShare

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[newtopbitoo]

Difference with my formula is that you first make variables, but actually it's the same with my formula isn't it ? 
The results are the same too