Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jz5147
Frequent Visitor

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.

jz5147_1-1635150593813.png

 



 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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. 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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. 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
speedramps
Super User
Super User

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.

Sorry maybe this is a better explanation of what i want to do.


The Quoted Revenue value for September 2021 is SUM(REVENUE) where QUOTED between to_date('2021-09-01', 'YYYY-MM-DD') and to_date('2021-09-30', 'YYYY-MM-DD')

Fsciencetech
Helper III
Helper III

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.

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.