Forum Discussion

hworrall's avatar
hworrall
Frequent Visitor
7 years ago

Complex query

Hi there,

 

I am new to Power BI and am trying to write a query where I need to subtract the result of one query from the result of a second query.  Here is what I have in SQL/English.  There are also two different date columns, it is not a typo.  I am not sure how to do it at all, but also would like to find the most efficient way to do it.

 

Any help is appreciated :)

 

Query 1:

Sum TotalCharge where:

 

SELECT Wdate, Wpdate, Whours, Wfee, Wexp, wFee+wExp+wSurchg as TotalCharge, Wdstamp, Wipdate, Windicator
FROM WIP
WHERE Wdate < '2018-05-01' AND Wipdate > '2018-04-30' AND (Windicator = 'w' or Windicator = 'h')

 

MINUS

 

Query 2:

Sum TotalCharge where:

SELECT Wdate, Wpdate, Whours, Wfee, Wexp, wFee+wExp+wSurchg as TotalCharge, Wdstamp, Wipdate, Windicator
FROM WIP

WHERE Wdate > '2018-04-30' AND Wipdate < '2018-05-01' AND (Windicator = 'w' or Windicator = 'h')

 

Thank you!

Need Help
Tips and Tricks
Tutorial Requests

1 Reply

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

    Hi hworrall ,

    please paste this code into the advanced editor and follow the steps:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0MLJR0wxxzGKQdiQwOlWB18ijKA2IiQokQgNkZTZInNOhNirDMlxjozNEXmUEVwDsg6c6CiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Wdate = _t, Wipdate = _t, Windicator = _t, TotalCharge = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Wdate", type date}, {"Wipdate", type date}, {"Windicator", type text}, {"TotalCharge", Int64.Type}}),
    Filter1 = Table.SelectRows(#"Changed Type", each [Wdate] < #date(2019, 5, 1) and [Wipdate] > #date(2018, 4, 30) and ( [Windicator] = "w" or [Windicator] = "h" ) ),
    Query1 = List.Sum(Filter1[TotalCharge]),
    Filter2 = Table.SelectRows(#"Changed Type", each [Wdate] > #date(2018, 4, 30) and [Wipdate] < #date(2019, 5, 1) and ( [Windicator] = "w" or [Windicator] = "h" ) ),
    Query2 = List.Sum(Filter2[TotalCharge]),
    Result = Query1 + Query2
in
    Result