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 i...
Need Help
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.

      • MojoGene's avatar
        MojoGene
        Post Patron
        6 years ago

        Well, I have stumbled on a DAX solution that looks like this:

        WIP Daily Balance 2 = 
CALCULATE (
    SUM ( fees_WIP[FeeValueWIP] ),
    FILTER (
        ALL ( fees_WIP ),
        fees_WIP[DateInWip] <= MAX ( fees_WIP[DateInWip] )
            && fees_WIP[DateOutWip] > MAX ( fees_WIP[DateInWip] )
    )
)

         When I try to use the Date table for comparison (e.g., ... fee_WIP[DateInWIP <= MAX(Dates[Date]) ...) that causes an error (which I still do not understand).

         

        Anyway, thanks for jumping in.

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

      Hi MojoGene ,

       

      did you solve your problem?

       

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

      Please give Kudos for support.