Calculating Backlog

[Anonymous]

Hello everyone! 🙂 

I am fairly new to PowerBI and am struggling with the calculation of a Bocklog with the following definition: 
Value of orders with requested delivery date in previous period that could have been invoiced if delivered (goods are not issued and invoiced in requested month).

The report should contain information about:- Value of Backlog sales orders per region.

So basically if the requested date was in the previous month and if the order has not yet been delivered, then the value of the order should be summed up.
Can anybody help? Thank you all in advance!

[Schmidtmayer]

First of all:  Welcome to the exciting world of Power BI 😃


It totaly depends on the data you have.
In case there is a table containing at least the following info, it is easy:
FactTable

orderID int,
value decimal,
regionID int,
requestdate date, 
shippingdate date (empty if not delivered)

the following might help:

BacklogLastMonth = CALCULATE(SUMX(FactTable, FactTable[value]), deliverdate = BLANK(), DATEDIFF(requestdate, NOW(), MONTH) = 1)

CALCULATE is basically used to overwrite filters during calculation only.
SUMX may be switched by SUM for this purpose.
After telling to CALCULATE what to calculate, (the sum in this case), you can add filters.
First one here being deliverydate = BLANK() for a shipping not to belivered and the second condition DATEDIFF(requestdate, NOW(), MONTH) = 1 means the requestdate was within the previous month, as requested.
For more info about CALCULATE, read the doc: CALCULATE function (DAX) - DAX | Microsoft Docs

[Anonymous]

Thank you very much, will try this and come back to (hopefully) report a success! 😄 

[Greg_Deckler]

@Anonymous Maybe:

Measure =
  VAR __Today = TODAY()
  VAR __EndofLastMonth = EOMONTH(__Today,-1)
  VAR __BeginofLastMonth = DATE(YEAR(__EndofLastMonth),MONTH(__EndofLastMonth),1)
RETURN
  SUMX(FILTER('Table',[RequestedDate]>=__BeginofLastMonth && [RequestedDate]<=__EndofLastMonth && [OrderFulfilled]="No"),[Value])

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.