Return value to a specific date

[Anonymous]

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

[Anonymous]

Hi,

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

[v-kelly-msft · Accepted solution]

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]

@Anonymous , expected output is not clear. try

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

[Anonymous]

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]

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!