Forum Discussion

elguestca1's avatar
elguestca1
Advocate I
2 years ago
Solved

Return earlier row value

Hello, 

I am facing an issue for which I can not find the answer. 

I need to return monthly change of a calculation in a measure. I get the correct values, but when I try to return previous month measure value in current month row it seems that Power BI also changes the aggregation it has inside, which is wrong for me.  

Example. 

So basicaly I just need to return previous difference value for current year-month row.

I need a measure and my dax is like this. Tried using PREVIOUSMONTH and other options but it returned the same value.

CALCULATE(
    [Difference],
    DATEADD('DataTable'[Date], -1, MONTH)
)


Maybe you guys have any ideas how to do it? Thank you.





Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi elguestca1 

     

    For your question, here is the method I provided.

     

    If [Difference] is measure, the code is as follows:

     

    Need = 
var _currentMonth = MAX('Table'[Year month])
var _previousMonth = MONTH(_currentMonth - 1)
RETURN
IF(
    MONTH(_currentMonth) <> 1,
    SUMX(
        FILTER(
            ALL('Table'),
            MONTH('Table'[Year month]) = _previousMonth
        ),
        'Table'[Difference]
    ),
    BLANK())

     

    If [Difference] is column, the code is as follows:

     

    PreviousMonthInCurrentRow = 
var _currentMonth = MAX('Table'[Year month])
var _previousMonth = MONTH(_currentMonth - 1)
RETURN
IF(
    MONTH(_currentMonth) <> 1,
    CALCULATE(
        SUM('Table'[difference]),
        FILTER(
            ALL('Table'),
            MONTH('Table'[Year month]) = _previousMonth
        )
    ),
    BLANK())

     

    Here is the result.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi elguestca1 

     

    For your question, here is the method I provided.

     

    If [Difference] is measure, the code is as follows:

     

    Need = 
var _currentMonth = MAX('Table'[Year month])
var _previousMonth = MONTH(_currentMonth - 1)
RETURN
IF(
    MONTH(_currentMonth) <> 1,
    SUMX(
        FILTER(
            ALL('Table'),
            MONTH('Table'[Year month]) = _previousMonth
        ),
        'Table'[Difference]
    ),
    BLANK())

     

    If [Difference] is column, the code is as follows:

     

    PreviousMonthInCurrentRow = 
var _currentMonth = MAX('Table'[Year month])
var _previousMonth = MONTH(_currentMonth - 1)
RETURN
IF(
    MONTH(_currentMonth) <> 1,
    CALCULATE(
        SUM('Table'[difference]),
        FILTER(
            ALL('Table'),
            MONTH('Table'[Year month]) = _previousMonth
        )
    ),
    BLANK())

     

    Here is the result.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • elguestca1's avatar
      elguestca1
      Advocate I
      2 years ago

      Hello, Anonymous ,

      I accept the solution for this, because for what i asked it gives the right answer. I tried editing your pbix file to make it more similar to my actual file and yes in your file I get the right answer, but for my file it has quite large data model and there are several many to many relationships which, I believe, disrupts the calculation of measure i need. Values used for Measure A and Measure B are not just simple column values, but it has filters from different tables while trying to calculate them. But thanks for your help, I'll try to use your solution to fix this.
      Thank you!