Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

visual calculation Previous in wrong column

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
 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for Kedar_Pande's concern about this issue.

     

    Hi, Anonymous 

    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.

     

4 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for Kedar_Pande's concern about this issue.

     

    Hi, Anonymous 

    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.