Cumulative sum between dates

[yossi_bire]

Hi

I'm trying to create a cumulative aggregation between dates.

I have an order table with ORDER_NUMBER, ORDERED_DATE, INVOICE_DATE, INCOME

The end result needs to be a Bar Chart that presents the income of the orders that the Quarter of the invoice greater from the ordered date

see the example below

1. The first order needs to be calculated for periods 2018-Q1 until 2018-Q3
2. The second order does not need to be calculated because of the ordered date and the invoice date in the same quarter
3. The Last order needs to be calculated for period 2018-Q2 only

ORDER_NUMBER	ORDERED_DATE	INVOICE_DATE	INCOME
11111	02/01/2018	02/10/2018	100
22222	02/05/2018	20/05/2018	300
33333	02/06/2018	03/08/2018	400

YearQuarter	INCOME
2018-Q1	100
2018-Q2	500
2018-Q3	100
2018-Q4	0

[zzyjordan]

Hi, 

Please find my solution by join your data to a calendar table as shown below

and then create a measure expression as below

Hope this helps

ZZ

[yossi_bire]

Hi,

Thanks, but the original table contains more than 80 columns, and I'm trying to avoid using IF statements.

Is there a way to do that with Aggr and RangeSum with Set Analysis?

[zzyjordan]

as the condition is dynamic, even with set analysis, you need some sort of if condition in the expression. And I am not quite understand why the number of columns will impact the if statements, as this is just compare two columns in your data set.