Forum Discussion

askpbiuser's avatar
askpbiuser
Icon for Helper I rankHelper I
2 years ago
Solved

DAX Previous value from measure

Hi All, I have two fact tables connected with a dimension table. The fact tables have a many-to-one relationship with Dimension. I have the below fields displayed in a table in Power BI visual. ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks lbendlin 

    Hi, askpbiuser 

    Let's assume that your A, B, Cumulative sum of A, and Cumulative sum of A-B are all metrics. If not, you should adjust my DAX expression below appropriately.

    I used the following DAX expression:

    Output1 =
VAR _currIndex =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _previousvalue =
    CALCULATE (
        [Measure of Cumulative sum of A-B],
        FILTER ( ALL ( 'Table' ), 'Table'[Index] < _currIndex )
    )
VAR _result = [Measure of Cumulative sum of A-B] - _previousvalue
RETURN
    SWITCH ( TRUE (), _result > 0, _result, 0 )

    Here are the results:

    I've uploaded the PBIX file I used this time below.

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

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

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

It is impossible to do this in DAX because DAX does not support conditional accumulation.  The only way to do this is in Power Query via List.Accumulate.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

askpbiuser's avatar
askpbiuser
Icon for Helper I rankHelper I
2 years ago

Thanks.
I have edited my post now to provide the Sample table and the Output I am looking for.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks lbendlin 

    Hi, askpbiuser 

    Let's assume that your A, B, Cumulative sum of A, and Cumulative sum of A-B are all metrics. If not, you should adjust my DAX expression below appropriately.

    I used the following DAX expression:

    Output1 =
VAR _currIndex =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _previousvalue =
    CALCULATE (
        [Measure of Cumulative sum of A-B],
        FILTER ( ALL ( 'Table' ), 'Table'[Index] < _currIndex )
    )
VAR _result = [Measure of Cumulative sum of A-B] - _previousvalue
RETURN
    SWITCH ( TRUE (), _result > 0, _result, 0 )

    Here are the results:

    I've uploaded the PBIX file I used this time below.

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

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