Forum Discussion
DAX Getting running total from Active orders
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:
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.
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.
2 Replies
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
Or
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.
