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: ...
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

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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 This is the second time you've literally saved my life. Thank you so much! Works like a charm.

  • 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
      Icon for Community Champion rankCommunity 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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Vvelarde These work perfectly. I really can't thank you enough! Thought I would never figure it out. Thanks again.