Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Calculate YTD Orders thru Current Month End

  • 1.  Calculate YTD Orders thru Current Month End

    Top Contributor
    Posted 2 days ago
    Hello:

    I am trying to calculate orders received with desired ship dates that go thru the end of this month. (The desired ship dates go further out but my question has to do with dynamically capturing orders thru end of current month based on desired ship date)

    The table I'm using has todays date as the slicer date. (5-04-2021).
    The  table is laid out by customer and has a lot of various measures like YTD, MTD, full month last year etc.

    The issue I'm having, in this context, us trying to sum up orders received with desired ship dates up to the end of this month. 5-31-2021.

    So while the slicer has todays date ,the result I'm hoping to achieve would be a cumulative amount thru the end of May. I've tried PARALELLE PERIOD, DATESBETWEEN, etc. and have had no luck. When I set the slicer to the end of May I do get correct results. I have fields in my tables with end of current month and other helper columns as an FYI.

    Any input on this measure is appreciated. I'll include a small image that gives an idea of the context. Thank you!




    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------


  • 2.  RE: Calculate YTD Orders thru Current Month End

    Silver Contributor
    Posted 2 days ago
    Hi @William Skelley

    Try this formula.

    Measure =
    CALCULATE([Total_Orders],
    DATESBETWEEN(OpenOrdersTable[RequiredShip],DATE(YEAR(TODAY()),MONTH(TODAY()),1),EOMONTH(TODAY(),0)))

    In this particular instance, Total_Orders is a measure that sums the booked sales using the date relationship between the Date table and the required ship date on the order table.  ​

    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------



  • 3.  RE: Calculate YTD Orders thru Current Month End

    Top Contributor
    Posted 2 days ago
    Hi Mary:

    Thank you very much for putting this suggestion together!

    I'll give it a go soon and let you know.

    Thanks in advance for your time on this!

    Best regards,
    Bill


    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------