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 loo...
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!

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
    Icon for Community Support rankCommunity 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!