Filtering new rows in data-table

[fheller98]

Good day to all,

Problem: I want a measure for the pipeline-value, which can be filtered for the specific user. Right now i get a row in the date table with the correct Pipeline-Value but i am not able to filter.

The following scenario:

I have a table for deals with our customers incl. creation date, closing date, the associated user & a deal value.

Now I would like to evaluate the total value of the pipeline of deals over time.

For this I have added a new column in the date table & calculated the value of the pipeline via a DAX formula:

Pipeline Worth =
VAR date1 = '- Date Table'[Date]

RETURN
CALCULATE(
SUMX('Deal-Table','Deal-Table'[Value])+0,
FILTER('Deal-Table','Deal-Table'[Create Date] < date1 && ('Deal-Table'[Close Date] > date1 || 'Deal-Table'[Close Date] = 0) )
)

Date Table
Date	Pipeline Value
01.12.2022	1.000,00 €
02.12.2022	1.000,00 €
03.12.2022	1.000,00 €
04.12.2022	1.000,00 €
05.12.2022	3.000,00 €
06.12.2022	2.000,00 €
07.12.2022	2.000,00 €
08.12.2022	2.500,00 €
09.12.2022	2.500,00 €
10.12.2022	2.000,00 €
11.12.2022	2.000,00 €
12.12.2022	2.800,00 €

Deal-Table
Create Date	Close Date	Deal-Name	Value	USER
01.12.2022	06.12.2022	Example 1	1.000,00 €	USER1
05.12.2022		Example 2	2.000,00 €	USER2
08.12.2022	10.12.2022	Example 3	500,00 €	USER3
12.12.2022		Example 4	800,00 €	USER4

[fheller98]

@Anonymous 

do you have any solution for this?

[Anonymous]

Hi @fheller98 ,

You can try this code to create a measure.

M_Pipeline Worth =
VAR date1 =
    SELECTEDVALUE ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        SUMX ( 'Deal-Table', 'Deal-Table'[Value] ) + 0,
        FILTER (
            'Deal-Table',
            'Deal-Table'[Create Date] <= date1
                && ( 'Deal-Table'[Close Date] > date1
                || 'Deal-Table'[Close Date] = 0 )
        )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fheller98]

@Anonymous 
Filtering now works - unfortunately I have another problem:

In the data model the Create-Date & the Date (Data-Table) are related to each other.
I think this leads to the fact that the pipeline value is separated on the individual date.

Do you have any solution for this?