cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AAMW01
Helper I
Helper I

DAX Getting running total from Active orders

I have an example table below,

 

OrderNumberRefDateOrderedDateOrderDuePrice x Quantity
1AP103/07/202101/09/20211200
2AP204/07/202108/07/202190
3AP310/07/202114/07/2021120
4AP417/07/202102/10/2021400
5AP518/07/202121/07/202155
6AP601/08/202104/08/202190
7AP702/08/202101/11/2021180
8AP804/08/202108/09/2021400
9AP905/08/202101/11/2021850

 

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
1 ACCEPTED SOLUTION

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.

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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-o...

 

Or

 

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!