Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Monthly Sum

Working towards building a formula, but I believe I need to do this in pieces so I'm not confusing anyone. I've asked this a few times and still haven't found my answer... So I will start simple:

 

How can I get the monthly sum for the latest month in this table? (71.28) I'm trying to set it up to where as new monthly revenue comes in, it is getting the total for the latest month and multiplying that figure by 12.

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    Anonymous

     

    Try putting the *12 after the last parenthesis.

     

    Regards

     

    Victor

    Lima - Peru

7 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    Anonymous Hi, you can use this measure:

     

     

    LastMonthRevenue =
VAR LastMonthRevenueDate =
    MONTH ( LASTDATE ( Table1[Revenue Date] ) )
VAR LastYearRevenueDate =
    YEAR ( LASTDATE ( Table1[Revenue Date] ) )
RETURN
    CALCULATE (
        SUM ( Table1[Revenue] );
        FILTER (
            Table1;
            MONTH ( Table1[Revenue Date] ) = LastMonthRevenueDate
                && YEAR ( Table1[Revenue Date] ) = LastYearRevenueDate
        )
    )

    Regards

     

    Victor

    Lima - Peru

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Vvelarde I get this when building the formula:

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        8 years ago

        Anonymous

         

        Try putting the *12 after the last parenthesis.

         

        Regards

         

        Victor

        Lima - Peru

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Vvelarde I do have a follow up question if you don't mind. So my supervisors are trying to decide whether they want to see the latest set of records or the 2nd to last set of records. So if I wanted to set it up based on the 2nd to last set of data, how could I modify the given formula to go back an additional month from the latest month?

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        8 years ago

        Anonymous

         

        Let's made a modification to the measure to be more simple

         

         

        LastMonthRevenue_Reloaded =
VAR LastMonthRevenueDate =
    EOMONTH ( LASTDATE ( Table1[Revenue Date] ); 0 )
RETURN
    CALCULATE (
        SUM ( Table1[Revenue] );
        FILTER ( Table1; EOMONTH ( Table1[Revenue Date]; 0 ) = LastMonthRevenueDate )
    )

        And -1 month:

         

        LastMonthRevenue_Reloaded-1 =
VAR LastMonthRevenueDate_1 =
    EOMONTH ( LASTDATE ( Table1[Revenue Date] ); -1 )
RETURN
    CALCULATE (
        SUM ( Table1[Revenue] );
        FILTER ( Table1; EOMONTH ( Table1[Revenue Date]; 0 ) = LastMonthRevenueDate_1 )
    )

        Regards

        Victor

        Lima - Peru