Measure to get the Cumulative Unreleased Orders

[Anonymous]

Could you please help. 

We are getting orders in sales department which need to be checked and released to production. There is order ID, value, order date and a flag whether it got released or not and the release date.

Order Date and Release Date are linked to Date Table with Order date as active and Release date as inactive releationship.

Order ID	Order Value	Order Date (Linked to Date Table) - Active	IsReleased	Release Date (Linked to Date Table) - InActive)
1	100	01-01-2023	1	04-01-2023
2	800	05-01-2023
3	900	08-01-2023	1	15-01-2023
4	400	05-02-2023	1	08-02-2023
5	600	15-02-2023	1	22-02-2023
6	300	20-02-2023	1	23-02-2023
7	700	01-03-2023
8	800	05-03-2023
9	500	10-03-2023
10	400	15-03-2023	1	20-03-2023
11	900	30-03-2023
12	500	05-04-2023	1	10-04-2023
13	600	10-04-2023
14	500	15-04-2023	1	20-04-2023
15	800	25-04-2023

Expecting the below cumulative unreleased order results to be populated in a chart.

Please support with DAX.

Date	UnReleased Order Value
01-01-2023	100
02-01-2023	100
03-01-2023	100
04-01-2023	0
05-01-2023	800
06-01-2023	800
07-01-2023	800
08-01-2023	1700
09-01-2023	1700
10-01-2023	1700
11-01-2023	1700
12-01-2023	1700
13-01-2023	1700
14-01-2023	1700
15-01-2023	800

On Jan 1st we got a order for 100$ and so the unrleased order value is 100 and it continues for next 4 days till it got released on Jan 4th and therefore the unreleased order value comes to 0 agian.

On Jan 5th we got order for 800$ and  it is not relased yet and on Jan 8th we got order for 900$ and so the total cumulative unreleased order value is 1700$ and it goes on till some orders gets added or some orders get relased.

Here I have given results till Jan 15. But in business scenario the data goes on for years and years. Please help with DAX for getting this cumulative values.

[Anonymous · Accepted solution]

Hi @Anonymous,

have created a simple sample , please refer to my  pbix file to see if it helps you.

Create a calendar date. Then merge the 2 tables.

From the “Get data” drop-down menu, select “Blank Query”. This will open the Power Query Editor window where you will build the table.

Create a list of dates using the formula bar. 

= List.Dates(#date(2023,1,1),365,#duration(1,0,0,0))

Then convert the List into a table by clicking the “To Table” button in the Convert menu on the ribbon. You will not make any selections r in the subsequent window, click “OK”.

Once the List has been converted to a Table, rename the column to “Date Value” and change the data type to a Date type using the data type button next to the column header.

More details about How to Create a Date Table 

Then merge the two tables.

Then create a measure.

result= 
VAR _1 =
    CALCULATE (
        SUM ( Merge1[Order Value]),
        FILTER (
            ALLSELECTED ( Merge1 ),
           Merge1[Query1.date] <= MAX (Merge1[Query1.date] )
                && Merge1[Attribute] <> "Release Date (Linked to Date Table) - InActive)"
        )
    )
VAR _2 =
    CALCULATE (
        SUM ( Merge1[Order Value]),
        FILTER (
            ALLSELECTED ( Merge1 ),
           Merge1[Query1.date] <= MAX (Merge1[Query1.date] )
                && Merge1[Attribute]  = "Release Date (Linked to Date Table) - InActive)"
        )
    )
RETURN
 _1-_2

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.