Forum Discussion

David_1970's avatar
David_1970
Frequent Visitor
8 years ago
Solved

Learning DAX

How do I create a measure calculating the difference in Money between Reveneu and Disbursement ONLY when Revenue > 0, i.e. when Revenue = 0 no subtraction should be made?

 

CustomerMoneyTypeDateMonth
1 Revenue201701
2365Revenue201702
3 Revenue201703
3 Revenue201704
2152Revenue201705
5325Revenue201706
4 Revenue201707
1 Disbursement201701
218Disbursement201702
332Disbursement201703
3 Disbursement201704
254Disbursement201705
5 Disbursement201706
469Disbursement201707
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi David_1970,

     

    If I understand you correctly, you should be able to firstly use the formula below to create a new calculate column in your table.

    Diff = 
IF (
    Table1[Type] = "Revenue"
        && Table1[Money] > 0,
    Table1[Money]
        - CALCULATE (
            SUM ( Table1[Money] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Customer] = EARLIER ( Table1[Customer] )
                    && Table1[Type] = "Disbursement"
                    && Table1[DateMonth] = EARLIER ( Table1[DateMonth] )
            )
        )
)

     

    Then use the formula below to create a new measure to calculate the difference in Money between Revenue and Disbursement in your scenario. :smileyhappy:

    Measure = SUM(Table1[Diff])

     

    Note: You will need to replace Table1 with your real table name in the formulas above.

     

    Regards

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi David_1970,

     

    If I understand you correctly, you should be able to firstly use the formula below to create a new calculate column in your table.

    Diff = 
IF (
    Table1[Type] = "Revenue"
        && Table1[Money] > 0,
    Table1[Money]
        - CALCULATE (
            SUM ( Table1[Money] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Customer] = EARLIER ( Table1[Customer] )
                    && Table1[Type] = "Disbursement"
                    && Table1[DateMonth] = EARLIER ( Table1[DateMonth] )
            )
        )
)

     

    Then use the formula below to create a new measure to calculate the difference in Money between Revenue and Disbursement in your scenario. :smileyhappy:

    Measure = SUM(Table1[Diff])

     

    Note: You will need to replace Table1 with your real table name in the formulas above.

     

    Regards

    • David_1970's avatar
      David_1970
      Frequent Visitor
      8 years ago

      Hi again,

       

      By the way, what is the ALL-function all about - I mean, isn't that function supposed to eliminate any filters?

       

      But since we are dealing with a calculated column (not a measure) there should be no filter context, or have I mixed things up?

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi David_1970,

         

        You're right! The ALL function is not needed here. Thanks for pointing it out. :smileyhappy:

         

        Regards