Forum Discussion

ClaireN's avatar
ClaireN
Frequent Visitor
5 years ago
Solved

Combine selected values from different columns into one column

Hi PBI people   My boss has come up with a method for forecasting that he wants in PBI (lets not question its logic lol). I have a SQL query that gives me the daily sales at whatever time of the...
Need Help
Tutorial Requests
  • ClaireN's avatar
    ClaireN
    5 years ago

    Thanks so much for your response it didn't quite work but was super close!, I ended up getting additional help and this is what worked - will post here in case anyone needs to do this: 

    the measure [today] = TODAY( )

     

     

    Combined Actual Sales and PreInvoice =
    VAR mySales =
    CALCULATE( [Sales Actual 2020],
    FILTER( ALLSELECTED( Dates[Date] ),
    Dates[Date] < [Today]
    ),
    VALUES( Dates[Date] )
    )
    VAR myPreInv =
    CALCULATE( [PreInvoice Future test revenue],
    FILTER( ALLSELECTED( Dates[Date] ),
    Dates[Date] >= [Today] &&
    Dates[Date] < [Today] +2
    ),
    VALUES( Dates[Date] )
    )
    RETURN
    mySales + myPreInv

     

     

     

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

ClaireN , Not very clear. But something like this should work

Cumm Sales =
var _max = maxx(allselected(Sales),sales[date])
return
CALCULATE(SUM(Sales[Sales Actual 2020]),filter(allselected(Sales),Sales[date] <=max(Sales[date]) && Sales[Sales] <=_max))
+ CALCULATE(SUM( PreInvoice Future test]),filter(allselected(Sales),Sales[date] <=max(Sales[date]) && Sales[Sales] <=_max +2 && Sales[Sales] >_max))