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

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

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

    Hi ，fheller98 

    What does the  'All Deals' Created date variable  mean?

    Please share your current formula and related screenshots.

     

    Best Regards,
    Community Support Team _ Eason

     

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

      Hey v-easonf-msft ,

      the

      Deals[CreateDate]

      is connected to

      Datumstabelle[Date]

       

       

      Therefore the new pipeline worth is zero.

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

        Hi, fheller98 

        These two dates are not related, and it is not suitable to create such a one-to-one bi-directional relationship.

        You can't create a relationship based on these two columns simply because they are both date fields.

        It is equal to permanently adding && Deals[CreateDate] = Datumstabelle[Date] when filtering data.

        e.g.

         

         

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

         

         

        It completely conflicts with the condition of  Deals[CreateDate]<Datumstabelle[Date] in my formula and you will get '0'.

         

        Please delete this relationship directly.

         

        Best Regards,
        Community Support Team _ Eason