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 following DAX-Function:

 

Pipeline Worth = CALCULATE(SUM('Deals'[Worth]),FILTER(ALL('Deals'),'Deals'[CreateDate]<CALCULATE(VALUES('Datumstabelle'[Date]&&'Deals'[CloseDate]>CALCULATE(VALUES('Datumstabelle'[Date]))))
 
I thought that this would present me the sumed up worth to each entry of the dates table (every date).
 
Sadly there is only one value coming up and not in relation to the "Date" from the Dates table.
 
Can anybody help me?
 
Thanks 
Finn
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

12 Replies

  • 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

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

    That worked perfectly. The only thing is, that this pipeline value is not filtering for mainly set filter.

     

    F.e. the pipeline deals is matched to an supervisor and therefore the pipeline value per supervisor would be very helpful to know.
    The Deals are matched to the supervisor and worth in one table but if i set the supervisor filter it is not changing the pipeline value.

     

    I know that we add && Deals[Supervisor] = "ljnas" to this:

    FILTER ( Deals, Deals[CreateDate] < date1 && Deals[CloseDate] > date1 )

     

    but we want a dynamic solution.

     

    Thanks for your help and greets

    Finn

    • v-easonf-msft's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi, fheller98 

      The value of calculated column will not be affected by the slicer. If you want to get dynamic values, you should try  measure.

      e.g.

       

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

       

      Please check my sample file. If I misunderstood , please let me know.

       

      Best Regards,
      Community Support Team _ Eason

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

        Hey v-easonf-msft ,

        that is exactly what i meant but it's not working in my powerBI sheet. I tried to remove one of the filter but it is not working either, so i guess it has something to do with the SelectedValue function. The 'All Deals' Created date variable is connected to the Date-variable. I think that is the "mistake" - do you have any solution for this?

         

        Thanks and greets from germany
        Finn