March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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!
Date | Sales Actual 2020 | PreInvoice Future test revenue | Sales 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 |
Solved! Go to Solution.
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
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
@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))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
122 | |
98 | |
89 | |
73 | |
64 |
User | Count |
---|---|
138 | |
115 | |
115 | |
98 | |
98 |