Remaining Amount Sum

[excelso21]

I have a situation where I have to sum the remaining values of a certain model.

My Table in the data model looks like this:

 ID Value Date    Amount  Creation Date
1   01/01/2018      1 000      01/01/2018
2   01/02/2018      1 000      01/01/2018
3   01/03/2018      1 000      01/01/2018
4   01/04/2018      1 000      01/01/2018
5   01/05/2018      1 000      01/05/2018
6   01/06/2018      1 000      01/01/2018
7   01/07/2018       0

The goal is to have a measure Remaining Value that would have this output:

Date   Remaining Value
Jan       5 000
Fev       4 000
Mar      3 000
Abr      2 000
Mai      2 000
Jun      1 000
Jul            0

Value Date is related to my calendar table.

In Jan, the logic is Sum Value Date >= 01/01/2018 with Creation Date >= 01/01/2018 and Creation Date<01/05/2018...

I tried the measure like this but is not working:

Remaining Value:=calculate(
			sum[Amount];
			filter(
				all('Calendar'[Date]);
				'Calendar'[Date]>max('Calendar'[Date]) &&
				'Calendar'[Date]<=max(Table[Creation Date]
			)
			)

[v-ljerr-msft · Accepted solution]

Hi @excelso21,

If I understand you correctly, the formula below should work in your scenario. 

Measure = 
VAR firstDateOfMonth =
    FIRSTDATE ( 'Calendar'[Date] )
VAR maxCreationDate =
    MAX ( Table1[Creation Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER (
            Table1,
            Table1[Creation Date] >= firstDateOfMonth
                && Table1[Creation Date] < maxCreationDate
        )
    )

Regards

[excelso21]

Thanks. It worked.

Alternatevily I figured out also this measure:

Measure:=-calculate(
			sum(Table1[Amount] );
			filter(
				all('Calendar');
				'Calendar'[Date]>max('Calendar'[Date])
				);
			filter(
				all(Table1[Creation Date]);
				Table1[Creation Date]<=max('Calendar'[Date])
			)					
)

[excelso21]

Hello, it is my first post so sorry if I don't use this Community standards.

I have a situation where I have to sum the remaining values of a certain model.

My Table in the data model looks like this:

 ID Value Date    Amount  Creation Date
1   01/01/2018      1 000      01/01/2018
2   01/02/2018      1 000      01/01/2018
3   01/03/2018      1 000      01/01/2018
4   01/04/2018      1 000      01/01/2018
5   01/05/2018      1 000      01/05/2018
6   01/06/2018      1 000      01/01/2018
7   01/07/2018       0

The goal is to have a measure Remaining Value that would have this output:

Date   Remaining Value
Jan       5 000
Fev       4 000
Mar      3 000
Abr      2 000
Mai      2 000
Jun      1 000
Jul            0

Value Date is related to my calendar table.

In Jan, the logic is Sum Value Date >= 01/01/2018 with Creation Date >= 01/01/2018 and Creation Date<01/05/2018...

I tried the measure like this but is not working:

 
Remaining Value:=calculate(
   sum[Amount];
   filter(
    all('Calendar'[Date]);
    'Calendar'[Date]>max('Calendar'[Date]) &&
    'Calendar'[Date]<=max(Table[Creation Date]
   )
   )