topic DAX Getting running total from Active orders in DAX Commands and Tips

DAX Getting running total from Active orders

AAMW01 — Thu, 05 Aug 2021 13:17:58 GMT

I have an example table below,

OrderNumber	Ref	DateOrdered	DateOrderDue	Price x Quantity
1	AP1	03/07/2021	01/09/2021	1200
2	AP2	04/07/2021	08/07/2021	90
3	AP3	10/07/2021	14/07/2021	120
4	AP4	17/07/2021	02/10/2021	400
5	AP5	18/07/2021	21/07/2021	55
6	AP6	01/08/2021	04/08/2021	90
7	AP7	02/08/2021	01/11/2021	180
8	AP8	04/08/2021	08/09/2021	400
9	AP9	05/08/2021	01/11/2021	850

It is linked to a calendar table on order date created by this measure:

Calendar Table = ADDCOLUMNS( CALENDAR(DATE(2019,01,01), DATE(2025,01,01)), "Year", YEAR([Date]), "Month Year Num", CONCATENATE(YEAR([Date]), FORMAT([Date],"MMM")), "Month Num", MONTH([Date]), "Month", FORMAT([Date], "MMM"), "Quarter Num", FORMAT([Date], "Q"), "Quarter", CONCATENATE("Q", FORMAT([Date], "Q")), "DAY", FORMAT([Date], "D"), "Week", FORMAT([Date], "WW") )

An active order is an order in the table where its due date is not past the date you are viewing at.

I want to get the total value of orders in (dateordered) - orders out (dateorderdue) as a line graph.

What I had was:

SOR Cumulative Total =

CALCULATE(SUM(SorSalesOrderBookLines[Price x Quantity]), DATESBETWEEN('Calendar Table'[Date], MAX(SorSalesOrderBookLines[DateOrdered]), MAX(SorSalesOrderBookLines[DateOrderDue]))

)

For example I want to view a point in the line graph "August 1st 2021" & I will see 1690. I know I need to link the 2nd date to a calendar but power bi does not allow this.

Please assist me, thank you

Re: DAX Getting running total from Active orders

amitchandak — Fri, 06 Aug 2021 06:06:03 GMT

AAMW01 , refer if one of the two can help you

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: DAX Getting running total from Active orders

AAMW01 — Fri, 06 Aug 2021 08:18:38 GMT

Thank you for the references,

I created a query which does what I wanted and I will post it here incase someone has a similar issue.

Running Total MEASURE 1 = CALCULATE ( SUM ( Sheet1[Price x Quantity] ), FILTER ( ALL ( 'Calendar Table' ), 'Calendar Table'[Date] <= MAX ( 'Calendar Table'[Date] ) ) ) - CALCULATE( SUM( Sheet1[Price x Quantity]), FILTER ( ALL ( Sheet1 ), Sheet1[DateOrderDue] <= MAX ( 'Calendar Table'[Date] ) ) )

I created a link between my order date in my order book table & calendar table.

I then created a formula to get the cumulative total of all orders added onto the system.

I then created a formulat to subtract the date order due transactions against the calendar date.