Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Return value to a specific date

Hi Power Bi Community !

 

I have struggled with calculating a variation betwen two specific dates, and I know it should be simple but my formula doesn't work and it drives me crazy !!

My table looks like this, and I've tried this formula (just to return for september 2020) : 

CALCULATE(sum('Table EBITDA'[Actual]),
filter(ALL('Table EBITDA'[Time_Cal]),'Table EBITDA'[Time_Cal]="septembre 2020"))
 
If anyone could help me, would be very much appreciated !!

 

 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous ,

     

    Modify your measure as below：

     

    Measure = CALCULATE(SUM('Table EBITDA'[Actual]),FILTER(ALL('Table EBITDA'),MONTH('Table EBITDA'[Time_Cal])=9&&YEAR('Table EBITDA'[Time_Cal])=2020))

     

    Or you may create a slicer table as below:

    slicer table = VALUES('Table EBITDA'[Time_Cal])

    Then create a measure as below:

    Measure 2 = CALCULATE(SUM('Table EBITDA'[Actual]),FILTER(ALL('Table EBITDA'),'Table EBITDA'[Time_Cal]=SELECTEDVALUE('slicer table'[Time_Cal])))

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , expected output is not clear. try

    CALCULATE(sum('Table EBITDA'[Actual]),
    filter(ALL('Table EBITDA'),'Table EBITDA'[Time_Cal]="septembre 2020"))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amit, 

      Thanks for your answer.

      I'd like to calculate the sum "Actual" of company A,B and C for september 2020. 

      However, when I apply my formula, my visual doesn't show the data. 

      I think the problem comes from the formatting of the date but I can't explain the reason...

      Any ideas?

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi  Anonymous,

         

        There is an error in your formula,you cant use "ALL('Table EBITDA'[Time_Cal])"and "'Table EBITDA'[Time_Cal]="septembre 2020" in the same filter which is contradictory,you need to modify your measure as below:

        Measure = CALCULATE(SUM('Table EBITDA'[Actual]),FILTER(ALL('Table EBITDA'),'Table EBITDA'[Time_Cal]="Septermber 2020"))

         

        And you will see:

        For the related .pbix file,pls see attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

    Thanks for your reply but still doesn't work, as "September 2020" isn't text but a month !

     

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      5 years ago

      Hi Anonymous ,

       

      Modify your measure as below：

       

      Measure = CALCULATE(SUM('Table EBITDA'[Actual]),FILTER(ALL('Table EBITDA'),MONTH('Table EBITDA'[Time_Cal])=9&&YEAR('Table EBITDA'[Time_Cal])=2020))

       

      Or you may create a slicer table as below:

      slicer table = VALUES('Table EBITDA'[Time_Cal])

      Then create a measure as below:

      Measure 2 = CALCULATE(SUM('Table EBITDA'[Actual]),FILTER(ALL('Table EBITDA'),'Table EBITDA'[Time_Cal]=SELECTEDVALUE('slicer table'[Time_Cal])))

      And you will see:

      For the related .pbix file,pls see attached.

       

      Best Regards,
      Kelly

      Did I answer your question? Mark my post as a solution!