DAX to Calculate SUM where one date is higher than another

[GarethWoodhouse]

Evening PowerBI people.

I am in need of some DAX assistance.

I have created a table called TodaysDate that hols todays date plus a measure to calculate 3 months back.

I have then written the below DAX in another table to Sum "Revenue" but only where the Date "Financial Period" > 3 months back

Total Revenue = calculate(SUM('Ex SP Figures'[Revenue]),'Ex SP Figures'[Financial Period]>TodaysDate[3monthsback])

The problem is the Calculate finction is complaining it's being used on a True/False Expression that is used as a table expression.

What am I doing wrong?

Many Thanks in advance.

[v-jiascu-msft]

Hi @GarethWoodhouse,

Could you please mark the proper answers as solutions?

Best Regards,
Dale

[Kristjan76 · Accepted solution]

You cannot use two columns in the calculate statement. These should work

Total Revenue = 
VAR d = MAX(TodaysDate[3monthsback]) 

RETURN
SUMX(
  FILTER(
    'Ex SP Figures',
    'Ex SP Figures'[Financial Period] > d
  ),
  'Ex SP Figures'[Revenue]
)

Total Revenue = 
calculate(
  SUM('Ex SP Figures'[Revenue]),
  FILTER(
    'Ex SP Figures'[Financial Period],
    'Ex SP Figures'[Financial Period]>MAX(TodaysDate[3monthsback])
  )
)

[TomMartens]

Hey,

please have a look at this site:

https://www.daxpatterns.com/time-patterns/

I guess, here you will your answer.

Regards,

Tom