Forum Discussion

MojoGene's avatar
MojoGene
Post Patron
6 years ago

Filter Values Between Dates

I am really struggling with this filter issue.

 

The table involved (Fees_WIP) contains Work in Progress values for a services company. The table includes the date the value was added to the Work in Progress (i.e., the date of the service) and the date the value was subtracted from Work in Progress (i.e., the date of payment for the service). A relationship exists between [DateInWIP] and the dates table.

 

I am trying to calculate the runing total WIP value for any given date. Conceptually, the total on any given date would be

the SUM of WIP where DateInWIP <= DATE && DateOutWIP > DATE.

 

I have made many attempts at this, but keep running into errors.

 

Thanks.

 

 

 

 

 

 

Need Help

6 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi MojoGene ,

     

    I would prefer a "entry" logic, with on date column

    Here an example:

    Date            | Entry Type | Quantity

    ------------------------------------------

    01-01-2019 | In WIP       | 10

    02-01-2019 | In WIP       |  20

    02-01-2019 |  Out WIP   | -10 

     

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

    • MojoGene's avatar
      MojoGene
      Post Patron
      6 years ago

      Thanks for the reply.

       

      I am having difficulty translating your logic to DAX. Can you enlighten me on that?

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        6 years ago

         Hi MojoGene ,

         

        i think you better do this with Power Query.

         

        1. Query for WIP IN

        2. Query for WIP OUT

         

        Then Append this two queries to one new Query.

         

        If I answered your question, please mark my post as solution, this will also help others.

        Please give Kudos for support.