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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Ram_DCT
Helper II
Helper II

Sum between two date columns with conditional start and end date

Hi Team,

 

I need a quick help - Below is my sample data;

asset_idCostreport_monthInitial_Investment_Date
10001-5657238.5231 December 201820 December 2018
100021416485.9713 June 201920 December 2018
10003-140632.9731 December 201817 December 2018
10004-164934.3531 December 201817 December 2018
10005-196886.1631 December 201817 December 2018
10006-281265.9431 December 201817 December 2018
10007-840321.1231 December 201820 December 2018
10008-379857.5713 June 201920 December 2018
10009-843797.8331 December 201817 December 2018
10010-1490709.531 December 201817 December 2018
10011-1503310.2231 December 201817 December 2018
10012-1828228.6431 December 201817 December 2018
10013-1879448.6131 December 201817 December 2018
10014-2630956.7531 December 201817 December 2018
10015-2812659.4531 December 201817 December 2018
10016-1969428.313 June 201912 June 2019

 

I need to get sum of the cost when;

1. report_month date >= initial_investment_date &&

2. report_month_quarter<= Selected - report_month_quarter

 

My report slicer is "report_month" as quarter and year. Hence, whenever I select my quarter of a year in slicer - I should get sum between above two condition 1 & 2.

 

Please help!!

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Ram_DCT ,

Here are the steps you can follow:

1. Create calculated table.

DateTable= CALENDARAUTO()

2. Create measure.

Measure =
var _select=SELECTEDVALUE('DateTable'[Date].[QuarterNo])
return
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),MAX('Table'[report_month])>=MAX('Table'[Initial_Investment_Date])&&QUARTER(MAX('Table'[report_month]))<=_select))

3. Result:

vyangliumsft_0-1625714919884.png

 

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

Hi  @Ram_DCT ,

Here are the steps you can follow:

1. Create calculated table.

DateTable= CALENDARAUTO()

2. Create measure.

Measure =
var _select=SELECTEDVALUE('DateTable'[Date].[QuarterNo])
return
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),MAX('Table'[report_month])>=MAX('Table'[Initial_Investment_Date])&&QUARTER(MAX('Table'[report_month]))<=_select))

3. Result:

vyangliumsft_0-1625714919884.png

 

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors