Last month sum

[Wresen]

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 as an example have 201905 in the slicer and have a measure that sums the month before (201904) or even better that i can choose the number of months back i want to sum.

Not sure how to do this , the "Month" column is TEXT

/Thanks

 

[sturlaws · Accepted solution]

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.

[v-jayw-msft · Accepted solution]

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]

Hi Jay

Sorry for a late reply.

Your code worked perfectly and i did a measure on -1 , and then i could choose how many months back in time i wanted to check.

Thanks so much .

I will "Accept your post as a solution" but i also want to say a big thanks to Sturla how also posted some help

[sturlaws · Accepted solution]

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.

[Wresen]

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]

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 )