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
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors