Conditional Sum based on Date

[rdraytonNBRS]

Hi All,

I have two fields (one "Revenue" and the other "WeightedAmt"), both of which have a relationship to a MonthEnd date field as per tables below.

I want to create a conditional measure that will return the Revenue field for all months incl last calendar month before today, but will return the WeightedAmt field for the current calendar month and future months.

Any help would be appreciated.

[amitchandak · Accepted solution]

@rdraytonNBRS ,

calculate(sum(Table[Revenue]), filter(Date, Date[Date]<=eomonth(today(),-1) ) )

or

calculate(sum(Table[Revenue]), filter(all(Date), Date[Date]<=eomonth(today(),-1) ) ) // use allselected if needed

for WeightedAmt 

calculate(sum(Table[WeightedAmt ]), filter(Date, Date[Date]> eomonth(today(),-1) ) )

or

calculate(sum(Table[WeightedAmt ]), filter(all(Date), Date[Date]> eomonth(today(),-1) ) ) // use allselected if needed