Forum Discussion

Ram_DCT's avatar
Ram_DCT
Icon for Helper II rankHelper II
5 years ago
Solved

Sum between two date columns with conditional start and end date

Hi Team,   I need a quick help - Below is my sample data; asset_id Cost report_month Initial_Investment_Date 10001 -5657238.52 31 December 2018 20 December 2018 10002 1416485.97 1...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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:

     

     

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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:

 

 

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