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?   Customer Money Type...
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

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