Forum Discussion

Wresen's avatar
Wresen
Icon for Post Patron rankPost Patron
6 years ago
Solved

Last month sum

Hi and thanks for reading this.   I have search for an answer for my little problem but i have not found it yet.   I have a table like below and a slicer that slices "month"   I would like to a...
Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi, Wresen,

     

    to create a measure that sum the previous month of the selected month from the slicer:

    Measure =
VAR _currentMonth =
    CALCULATE ( SELECTEDVALUE ( 'Table'[month] ); ALLSELECTED ( 'Table' ) )
VAR _prevMonth =
    CALCULATE (
        MAX ( 'Table'[month] );
        FILTER ( ALL ( 'Table'[month] ); 'Table'[month] < _currentMonth )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[count] );
        FILTER ( ALL ( 'Table'[month] ); 'Table'[month] = _prevMonth )
    )

     

    When it comes to this part of your post: or even better that i can choose the number of months back i want to sum, it is not clear to me what you want to achieve. Or what I mean, what you are descibing here, if I understand correctly, is very easy to achieve in Power BI: Just create a measure m=SUM([Count]) and it will sum the values from the months you choose in the slicer(or all months if there is no selection in the slicer)

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    hi Wresen ,

     

    Please check following measure and see if it achieve your exception:

     

    Measure =

    VAR sv =

              EDATE (

                   DATE ( MID ( SELECTEDVALUE ( 'Table'[month] ), 1, 4 ), MID ( SELECTEDVALUE ( 'Table'[month] ), 5, 2 ), "01" ),

                 -1

              )

    RETURN

              CALCULATE (

                   SUM ( 'Table'[count] ),

                 FILTER (

                      ALL ( 'Table' ),

                      DATE ( MID ( 'Table'[month], 1, 4 ), MID ( 'Table'[month], 5, 2 ), "01" ) = sv

                   )

    )

     

    Result would be shown as below:

    BTW, Pbix as attached. Hopefully works for you.

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Wresen's avatar
Wresen
Icon for Post Patron rankPost Patron
6 years ago

HI Sturla

 

Thanks so much for your answer

Your code works perfect as i wanted , the sum the  previous month.

What i mean by "even better that i can choose the number of months back i want to sum," is :

 

The slicer is set on 201905 but the measure code sums not the previous month(201904 values) but previous month -1 (201903 values)

 

/thanks

sturlaws's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Ok, then it is a bit more tricky.

 

you need to create a table dedicated for a slicer with the number of months to go back, without any relationships. So create a table 'Months back' with the column [Months], with values e.g. 1,2,3,4,5

 

Then create this measure: 

measure =
VAR _monthsBack =
    CALCULATE ( SELECTEDVALUE ( 'Months back'[Months]; 1 ) )
VAR _currentYearMonthNum =
    CALCULATE ( MAX ( 'Table'[yearMonthNum] ); ALLSELECTED ( 'Table' ) )
VAR _pastYearMonthNum =
    CALCULATE (
        MAX ( 'Table'[yearMonthNum] );
        FILTER (
            ALL ( 'Table'[yearMonthNum] );
            'Table'[yearMonthNum] < _currentMonth - _monthsBack
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[count] );
        FILTER (
            ALL ( 'Table'[yearMonthNum] );
            'Table'[yearMonthNum] = _pastYearMonthNum
        )
    )

 

You need to add a calculated column to your table for this to work:

yearMonthNum =
 ( LEFT ( month, 4 ) - 2019 ) * 12
    + RIGHT ( month, 2 )