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

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

     
    • 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.