Forum Discussion

fheller98's avatar
fheller98
Helper I
3 years ago

Filtering new rows in data-table

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
DatePipeline Value
01.12.20221.000,00 €
02.12.20221.000,00 €
03.12.20221.000,00 €
04.12.20221.000,00 €
05.12.20223.000,00 €
06.12.20222.000,00 €
07.12.20222.000,00 €
08.12.20222.500,00 €
09.12.20222.500,00 €
10.12.20222.000,00 €
11.12.20222.000,00 €
12.12.20222.800,00 €
Deal-Table
Create DateClose DateDeal-NameValueUSER
01.12.202206.12.2022Example 1   1.000,00 € USER1 
05.12.2022 Example 2   2.000,00 € USER2 
08.12.202210.12.2022Example 3       500,00 € USER3 
12.12.2022 Example 4       800,00 € USER4 
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      fheller98
      Helper I
      3 years ago

      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?