locked
Calculating order intake via orderbook and revenue RRS feed

  • Question

  • Hi,

    I would like to calculate our order intake figures, by substracting todays order book from yesterdays orderbook and then adding the revenue. The formula is like this:

    Order intake (yesterday) = Orderbook(today morning) - Orderbook(yesterday morning) + Revenue(during yesterday)

    My tables looks like this:


    My problem is how do I get yesterdays orderbook? I have considered making it as a calculated column, but that is perhaps not a good idea as my real world model also includes which sales orders, countries etc are in the orderbook/revenue.

    Tuesday, February 28, 2017 12:28 PM

Answers

  • Hi Morten_DK,

    You can use the DATEADD function, create a measure like the following formula. You can replace the COUNTA function based on your requirement. Calculate Revenue(during yesterday) using similar formula.

    yesterday:=CALCULATE(COUNTA(Backlog[Orderbook]),DATEADD(Calendar[Date],-1,day))

    Please let me know if you have any issue.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Imke FeldmannMVP, Editor Saturday, March 4, 2017 7:43 AM
    • Marked as answer by Morten_DK Tuesday, March 7, 2017 6:32 PM
    • Unmarked as answer by Morten_DK Tuesday, March 7, 2017 6:43 PM
    • Marked as answer by Morten_DK Friday, April 7, 2017 9:55 AM
    Wednesday, March 1, 2017 2:19 AM

All replies

  • Are you using today's date or the date of the record?

    Is there more than one record per day?

    If today's date, then subtract one day to filter to yesterday's records for revenues. 

    If a running thing the filters would be based on the date in an order record and the filter that date - 1 day

    Tuesday, February 28, 2017 12:41 PM
  • I would be the day of the record.

    Yes there can be many records each day - each sales order is their own record. So it would likely be around 60.000 records per day in the orderbook.

    Revenue is okay as it is, it is coming from the source system in the format that is needed (daily basis), it is the orderbook that is the main issue, how to calculate if I choose the 23-02-2017 that the orderbook is then 22-02-2017, but also if I put months in the rows that the calculation is then covering entire for instance February. 

    Tuesday, February 28, 2017 2:19 PM
  • Hi Morten_DK,

    You can use the DATEADD function, create a measure like the following formula. You can replace the COUNTA function based on your requirement. Calculate Revenue(during yesterday) using similar formula.

    yesterday:=CALCULATE(COUNTA(Backlog[Orderbook]),DATEADD(Calendar[Date],-1,day))

    Please let me know if you have any issue.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Imke FeldmannMVP, Editor Saturday, March 4, 2017 7:43 AM
    • Marked as answer by Morten_DK Tuesday, March 7, 2017 6:32 PM
    • Unmarked as answer by Morten_DK Tuesday, March 7, 2017 6:43 PM
    • Marked as answer by Morten_DK Friday, April 7, 2017 9:55 AM
    Wednesday, March 1, 2017 2:19 AM
  • Hi Angelia, 

    That works fine as long as I don't select any specific month. But if I choose for instance the month of February instead of having having each day in the rows  I get an error that "Function 'DATEADD' only works with contiginous date selections"   How to work around that one?

    Best regards

    Morten

    Tuesday, March 7, 2017 6:50 PM
  • Hi Morten_DK,

    'DATEADD' only works with contiginous date selections. Your Date column is breaken when you choose for instance the month of February instead of having having each day in the rows? If it is, You'd better ceate a calculated column to get yesterday using the following formulas.

    Rank=RANKX(Table, Table[Date],ASC)
    
    Yesterday=LOOKUP(Table[Date], Table[Rank],Table[Rank]-1)
    Then create count using yesterday field.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 10, 2017 3:26 AM