Forum Discussion

JezMazter's avatar
JezMazter
Frequent Visitor
3 years ago
Solved

Formula Calculate between 2 queries

Hello, 

 

I am currently trying to create a calculation between 2 queries.

 

-one query for Current Open backlog (weekly)

-one query for Created and Closed (weekly)

 

I am trying to calculate a reassigned (there is no data source, this number can be negative/positive). This is determined by this calculation:

Week 2 - Week 1 = Created - Closed + Reassigned  (illustrated below)

 

 

 

Is this something feasible or I may possibly need to just manually calcuate ? 

 

Thanks in adavnce.

Need Help
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, JezMazter 

     

    You can try the following methods.

    Measure1 = 
CALCULATE ( SUM ( Table1[Value1] ),
    FILTER ( ALL ( Table1 ), [Week] = MAX ( Table1[Week] ) )
)
    - CALCULATE ( SUM ( Table1[Value1] ),
        FILTER ( ALL ( Table1 ), [Week] = MAX ( Table1[Week] ) - 1 )
    )
    Measure2 = 
CALCULATE ( SUM ( Table2[Value2] ),
    FILTER ( ALL ( Table2 ), [Status] = "Created" )
)
    - CALCULATE ( SUM ( Table2[Value2] ),
        FILTER ( ALL ( Table2 ), [Status] = "Closed" )
    )
    Reassigned = [Measure1]-[Measure2]

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, JezMazter 

     

    You can try the following methods.

    Measure1 = 
CALCULATE ( SUM ( Table1[Value1] ),
    FILTER ( ALL ( Table1 ), [Week] = MAX ( Table1[Week] ) )
)
    - CALCULATE ( SUM ( Table1[Value1] ),
        FILTER ( ALL ( Table1 ), [Week] = MAX ( Table1[Week] ) - 1 )
    )
    Measure2 = 
CALCULATE ( SUM ( Table2[Value2] ),
    FILTER ( ALL ( Table2 ), [Status] = "Created" )
)
    - CALCULATE ( SUM ( Table2[Value2] ),
        FILTER ( ALL ( Table2 ), [Status] = "Closed" )
    )
    Reassigned = [Measure1]-[Measure2]

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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