Forum Discussion
SUM Based on FIELD Date Range
Hi there,
I am having some trouble getting the results required. Any help would be much appreciated.
REVENUE = Currency Field
QUOTED = Date
September 2021 = Calendar Date
I am trying to do the below
QUOTED
September 2021 is SUM(REVENUE)
QUOTED between to_date('2021-09-01', 'YYYY-MM-DD') and to_date('2021-09-30', 'YYYY-MM-DD')
October 2021 is SUM(REVENUE)
QUOTED between to_date('2021-10-01', 'YYYY-MM-DD') and to_date('2021-10-31', 'YYYY-MM-DD')
Below in RED is the numbers i am trying to get.
Hi,
Create a Calendar Table with a replationship from the Date column of the 2 tables to the date column of the Calendar Table. Create Year, Month Name and Month number columns in the Calendar Table. Sort the Month name column by the Month number column. To your visual, drag Year and Month name columns from the Calendar Table. Write this measure:
Total revenue = sum(Data[Revenue])
Hope this helps.
4 Replies
On the visual, filter context provide the sum of revenue for each month. No need to code between the dates.
Your question is not clear.
Please can you try explain the problem a little better, I don't understand it. Thanks.
Try show your reequired equation step by step and ensure the fields in the equation correspond exactly to the screen print.
Hi,
Create a Calendar Table with a replationship from the Date column of the 2 tables to the date column of the Calendar Table. Create Year, Month Name and Month number columns in the Calendar Table. Sort the Month name column by the Month number column. To your visual, drag Year and Month name columns from the Calendar Table. Write this measure:
Total revenue = sum(Data[Revenue])
Hope this helps.
