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

3 Replies

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

       

  • excelso21's avatar
    excelso21
    Frequent Visitor
    8 years ago

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