Calculate amount of open order for previous months

[Ben-Jy]

Decidedly, I have a lot of trouble with dates.
I have a table that lists the paid orders, with an amount, a start date and a payment date.
I would like to make a measurement that calculates the total amount of open orders per month.
So per month, I want to calculate the sum of the amounts of orders that are still open, therefore not yet paid.
For the month of August, for example, I would like to calculate the total amount of orders that were created before/during August and that were paid after August.
That way, for any month, we could know how many open orders we had.

A picture is worth a thousand words; if I have this table :

Order date :	Payment date :	Amount :
01.01.2020	20.08.2020	100
03.01.2020	02.04.2020	56
26.01.2020	02.02.2020	25
06.02.2020	06.06.2020	31
27.02.2020	04.05.2020	65
03.03.2020	13.05.2020	78
16.03.2020	13.07.2020	111
12.04.2020	15.04.2020	210
23.04.2020	01.05.2020	130
18.05.2020	16.06.2020	54
03.06.2020	21.08.2020	89
23.06.2020	01.08.2020	67
15.07.2020	17.07.2020	45
16.08.2020	06.09.2020	31

So, if we want to show the total amount of orders that were open during the month of March, we would have all the order which I've highlighted in bold.

If we want to show the total amount of orders that were open during the month of May, we would have all the order which I've underlined.

I need this information to have like a history of the previous months, not only the information for the actual date (TODAY())

Hope it's clear.

Thank you 🙂

[jdbuchanan71]

@TwinJohnson 

That would be something along these lines.

Open Orders = 
VAR _first = FIRSTDATE ( Dates[Date] )
VAR _last = LASTDATE ( Dates[Date] )
RETURN
CALCULATE(
    [Order Amount],
    Orders[Order Date] <= _last,
    ( Orders[Payment Date] >= _first || ISBLANK ( Orders[Payment Date] ) ),
    CROSSFILTER(Dates[Date],Orders[Order Date],None)
)

[TwinJohnson]

thank you so much @jdbuchanan71 , worked perfectly.

[v-lili6-msft]

hi  @Ben-Jy 

Here is a similar post for you refer to;

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

for your case, just add a Calendar table, then create a measure as below:

Total Amount = 
VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Table',"MonthYearBegin",VALUE(YEAR([Order date :]) & FORMAT(MONTH([Order date :]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR([Payment date :]) & FORMAT(MONTH([Payment date :]),"0#")))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Year",[Year],
    "Month",[Month],
    "Amount",[Amount :]
)
RETURN SUMX(tmpTable,[Amount])

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[amitchandak]

@Ben-Jy , refer if this blog.  A formula current employee should work for you. There is prior formula also there .

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[jdbuchanan71]

@Ben-Jy 

I have added 2 measures in my sample, the first is just the sum of order amount.

Order Amount = SUM ( Orders[Amount] )

The second calcuates the order amount based on the order date and paid date.  It assumes there is a dates table and the dates table is linked to the orders table on the order date.  For the measure to work we have to deactivate the relationship.

Open Orders = 
VAR _first = FIRSTDATE ( Dates[Date] )
VAR _last = LASTDATE ( Dates[Date] )
RETURN
CALCULATE(
    [Order Amount],
    Orders[Order Date] <= _last,
    Orders[Payment Date] >= _first,
    CROSSFILTER(Dates[Date],Orders[Order Date],None)
)

I have attached my sample file for you to look at.

[TwinJohnson]

@jdbuchanan71 @ I have a similar problem but if the payment date is blank then this should be counted as an open order too, what would the formula be if to sum the total between the last and first and blanks

Thank you,

 Orders[Order Date] <= _last,
    Orders[Payment Date] >= _first,

[Anonymous]

nvm