Sales order value for sales due before end of the month

[PaulRyan]

Hello all, New boy here

I`m just starting with BI and I`ve done some pretty good (in my opinion) reports already. But as with stuff like this the more you give people the more they want.

I need to be able to calculate the value of sales in our books which are due before the end of this month to give a "potential" of sales for this month if we manage to despatch everything. This calculation needs to look at all sales orders which have a due before the end of the month so they could be from previous months. Because this report will be for the directors I want this report to work without needing any input from the user, that is to say no matter what the month the report should always return a value for the current month.

I`ve been looking at EOMONTH and ENDOFMONTH but I`m no wiser I`m afraid.

[kentyler]

I made up a little orders table,

If there might be any other filters in the filter context you might want to add an ALL(Orders) into the filters for Calculate so that you would get all of the orders, regardless of any outside filter context.

I am a personal Power BI trainer. 

[PaulRyan]

@kentyler 

I tried that Ken and got this

The data is in the salesorderitems table, the sales order value is remain, the dates are duedate.

[kentyler]

If you'd like to do a quick screen share. Send me your email and I'll send you a meeting invitation.

[kentyler]

That should work. Can you send me a small copy of the power bi file ? I'd like to know what is going on.

[PaulRyan]

@kentyler 

Hi Ken, hopefuly you can get this from the link. I`ve created a new bi file to simplify things. It now only has one table from sql bsp_saleshistory365days, I`ve calculated the remain value from quantity-qty delivered (so we`re only looking at what`s left in the order) and the planneddeldate is the date we`re interested in. I`d need to create a column for [remain]*[unitprice] to get the value but I can do that okay I just need to kow how to restrict the results to anything with a planneddeldate which is less than the end of the current month (automatically selected). Also this needs to work for a DirectQuery as I need the data to be live.

potential bi 

[amitchandak]

This one will give you data for that month till month-end

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH'Date'[Date Filer])))

But you want to get data from past months too, then you might change the date join, put a filter of due.

On due = 
Var   _This_month=(max(endofmonth('Date'[Date Filer])))

return 
CALCULATE(sum(Sales[Sales Amount]),(Sales[Due Date])<=_This_month)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601.

[PaulRyan]

@amitchandak 

Thanks for the very quick reply, as I said I`m a new boy. Where do I put your script?