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 mon...
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.

     

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!