Forum Discussion

excelso21's avatar
excelso21
Frequent Visitor
8 years ago
Solved

Remaining Amount Sum

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    ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi excelso21,

     

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

    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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi excelso21,

 

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

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's avatar
    excelso21
    Frequent Visitor
    8 years ago

    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])
			)					
)