Forum Discussion
Conditional Sum based on Date
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.
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
1 Reply
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