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

Stuck: Finding Max Date for a Order and Movement type Prior to another Movement

  • 1.  Stuck: Finding Max Date for a Order and Movement type Prior to another Movement

    Bronze Contributor
    Posted 25 days ago
    Hi all,

    I am hoping that someone may be able to assist with a DAX expression.
    I am struggling to pull together the Maximum Posting Date for a specific Order & Movement Type that falls before a specific Movement Type.

    Let me show you, in example below.
    I need to determine the 101 date that comes before the 102 date.
    I created a measure to get the Max101Date (
    Last101 = MAXX(MyTable, IF(MyTable[Movement Type] = "101", MyTable[Posting Date]))

    I created another measure to get the First 102 Posting Date

    First102 = MINX(MyTable, IF(MyTable[Movement Type] = "102", (MyTable[Posting Date])))

    See data below.
    Order Posting Date Movement Type
    208455961 11/29/2019 261
    208455961 11/30/2019 261
    208455961 12/1/2019 261
    208455961 12/2/2019 261
    208455961 12/4/2019 261
    208455961 12/8/2019 261
    208455961 12/16/2019 261
    208455961 12/18/2019 261
    208455961 12/20/2019 101
    208455961 12/21/2019 101
    208455961 1/6/2020 102
    208455961 1/13/2020 101
    208455961 1/14/2020 261


    How do I pull the Max 101 Movement Date prior to 102 Movement.  In example above the date I need is 12/21/2019.

    Thanks



    ------------------------------
    Corinna
    MI
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Stuck: Finding Max Date for a Order and Movement type Prior to another Movement

    Gold Contributor
    Posted 25 days ago

    I guess this is what you were looking for. Try it with different dates I hope its gonna work.

    I created two measures: first one
    and the second one:

    I hope its gonna be of some help.





    ------------------------------
    Imran Ahmed
    ------------------------------

    Attachment(s)

    xlsx
    movement.xlsx   9K 1 version
    pbix
    movement.pbix   22K 1 version
    Academy - Online Interactive Learning from Experts