Filter Values Between Dates

[MojoGene]

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.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[mwegener]

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]

Thanks for the reply.

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

[mwegener]

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.

[mwegener]

 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]

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.