Forum Discussion

fheller98's avatar
fheller98
Icon for Helper I rankHelper I
5 years ago
Solved

Pipeline value - SUMIF between Dates

Hey there,   i have a table with a deal, the worth, create-date and close date. In order to create a line diagramm of our pipeline-worth i wanted to create a new column in my dates table with follo...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi,  fheller98 

    Try measure as below:

    Pipeline Worth = 
VAR date1 = Datumstabelle[Date]
VAR tab =
    FILTER ( Deals, Deals[CreateDate] < date1 && Deals[CloseDate] > date1 )
RETURN
    SUMX ( tab, Deals[Worth] )+0

    Notice to change the format of the measure.

    The result will show as below:

    Please check my sample file for more details.

     

    Best Regards,
    Community Support Team _ Eason

fheller98's avatar
fheller98
Icon for Helper I rankHelper I
5 years ago

Hey amitchandak,

 

thanks for your fast response. Below you will find the two tables. In addition i created a "solution" table due to the fact, that i know how to create it in excel.

 

Datemonthcalenderweekyear
01.01.2021112021
02.01.2021112021
03.01.2021112021
04.01.2021122021
05.01.2021122021
06.01.2021122021
07.01.2021122021
08.01.2021122021
09.01.2021122021
10.01.2021122021
11.01.2021132021
12.01.2021132021
13.01.2021132021
14.01.2021132021
15.01.2021132021
16.01.2021132021
17.01.2021132021
18.01.2021142021
19.01.2021142021
20.01.2021142021
21.01.2021142021
22.01.2021142021
23.01.2021142021
24.01.2021142021
25.01.2021152021
26.01.2021152021
27.01.2021152021
28.01.2021152021
29.01.2021152021
30.01.2021152021
31.01.2021152021

 

Deal-IDWorthCreate-DateClose-Date
123       100,00 €03.01.202106.01.2021
124       110,00 €04.01.202107.01.2021
125       120,00 €05.01.202108.01.2021
126       130,00 €06.01.202109.01.2021
127       140,00 €07.01.202110.01.2021
128       150,00 €08.01.202111.01.2021
129       160,00 €09.01.202114.01.2021
130       170,00 €10.01.202115.01.2021
131       180,00 €11.01.202116.01.2021
132       190,00 €12.01.202117.01.2021
133       200,00 €13.01.202118.01.2021
134       210,00 €14.01.202119.01.2021
135       220,00 €15.01.202117.01.2021
136       230,00 €16.01.202118.01.2021
137       240,00 €17.01.202119.01.2021
138       250,00 €18.01.202120.01.2021
139       260,00 €19.01.202121.01.2021
140       270,00 €20.01.202122.01.2021

 

DatemonthcalenderweekyearPipeline_worth
01.01.20211120210
02.01.20211120210
03.01.20211120210
04.01.2021122021100
05.01.2021122021210
06.01.2021122021230
07.01.2021122021250
08.01.2021122021270
09.01.2021122021290
10.01.2021122021310
11.01.2021132021330
12.01.2021132021510
13.01.2021132021700
14.01.2021132021740
15.01.2021132021780
16.01.2021132021820
17.01.2021132021640
18.01.2021142021450
19.01.2021142021250
20.01.2021142021260
21.01.2021142021270
22.01.20211420210
23.01.20211420210
24.01.20211420210
25.01.20211520210
26.01.20211520210
27.01.20211520210
28.01.20211520210
29.01.20211520210
30.01.20211520210
31.01.20211520210
v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi,  fheller98 

Try measure as below:

Pipeline Worth = 
VAR date1 = Datumstabelle[Date]
VAR tab =
    FILTER ( Deals, Deals[CreateDate] < date1 && Deals[CloseDate] > date1 )
RETURN
    SUMX ( tab, Deals[Worth] )+0

Notice to change the format of the measure.

The result will show as below:

Please check my sample file for more details.

 

Best Regards,
Community Support Team _ Eason