Forum Discussion

cristianml's avatar
cristianml
Icon for Post Prodigy rankPost Prodigy
7 years ago
Solved

Dax formula calculate Difference cumulative

Hi,

 

I need to replicate the following formula as a measure for PBI : 

Column D (Acumulative) + Column C - Column B

How can I calculate this?

 

Thanks,

General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi cristianml 

    You may try below measure:

    Measure =
IF (
    MAX ( Table1[A] ) = BLANK (),
    SUM ( Table2[D] ),
    CALCULATE (
        SUM ( Table2[D] ) + SUM ( Table1[C] )
            - SUM ( Table1[B] ),
        FILTER ( ALL ( Table1 ), Table1[A] <= MAX ( Table1[A] ) )
    )
)

    Regards,

4 Replies

  • cristianml's avatar
    cristianml
    Icon for Post Prodigy rankPost Prodigy
    7 years ago

    Hi, 

     

    I would like to replicate the following formula in Dax: 

    "Inventory" (Column D) + "Revenue" (C) - "Billings" (B)

     

    The thing is that I have "Revenue" and "Billings" in same column in my query in Power BI:

     

     

     

    Thanks,

     

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi cristianml 

    You may try below measure:

    Measure =
IF (
    MAX ( Table1[A] ) = BLANK (),
    SUM ( Table2[D] ),
    CALCULATE (
        SUM ( Table2[D] ) + SUM ( Table1[C] )
            - SUM ( Table1[B] ),
        FILTER ( ALL ( Table1 ), Table1[A] <= MAX ( Table1[A] ) )
    )
)

    Regards,

    • cristianml's avatar
      cristianml
      Icon for Post Prodigy rankPost Prodigy
      7 years ago

      Hi v-cherch-msft ,

       

      Finally I solved it with the following formula:

       

      Actual Inventory =
      CALCULATE([Actual Revenue] - [Actual Billings],FILTER(ALLSELECTED('List Period'),'List Period'[Date] <= MAX('List Period'[Date])))
       
      Thanks & Regards