Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ClaireN
Frequent Visitor

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 day it is refreshed (this value will increase as the day progresses). Plus whats know as preinvoice revenue, which is sort of a prediction of what $$ will be coming in the future (ignore the fact that sales actual and preinvoice in the past don't really match up - thats some wizardness in the system only a few understand).

 

I need to take all the values from the daily sales column (except the current day, as not all sales will be in the system) and two values from the preinvoice column and combine them into one column.

 

Is this possible in DAX? 

 

Say today is 13/11/20 and I need to run the forecast.  In the table below I've purposely ignored the $25,000 in the daily sales column (as that is todays date where I am and an incomplete day of sales) and taken the Preinvoice value from the 13/11/20 and 14/11/20 into my "Sales Actual + 2 days preinvoice" column. 

 

Can I automate this? Any ideas - I don't any know R 😞

 

I've then got to make a cumulative plot of "sales actual 2020" (excluding the current day) and a cumulative plot of "sales actual + 2 days preinvoice" and find the equation of the line to then predict what the sales will be at the end of the month (line is forced through zero, so in excel its something like y = 154966x. I found a youtube video that may be able to help with that part but I have no idea how to make the combined column (sales actual + 2 days pre invoice). 

Much appreciate any attempts at this!

 

DateSales Actual 2020PreInvoice Future test revenueSales Actual + 2 days Preinvoice   
1/11/2020$126,000$170,123$126,000   
2/11/2020$201,456$133,456$201,456   
3/11/2020$81,563$71,122$81,563   
4/11/2020$190,552$143,450$190,552   
5/11/2020$204,222$193,620$204,222   
6/11/2020$153,456$176,187$153,456   
7/11/2020$123,456$174,182$123,456   
8/11/2020$87,000$151,908$87,000   
9/11/2020$225,000$128,532$225,000   
10/11/2020$123,456$131,458$123,456   
11/11/2020$225,010$148,834$225,010   
12/11/2020$159,123$172,066$159,123   
13/11/2020$25,000$210,258$210,258   
14/11/2020 $140,957$140,957   
15/11/2020 $120,627    
1 ACCEPTED SOLUTION
ClaireN
Frequent Visitor

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

 

 

 

 

 

View solution in original post

2 REPLIES 2
ClaireN
Frequent Visitor

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
Super User
Super User

@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))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.