cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wresen Regular Visitor
Regular Visitor

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

 

month2.png 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
sturlaws New Contributor
New Contributor

Re: Last month sum

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.

 

View solution in original post

v-jayw-msft Senior Member
Senior Member

Re: Last month sum

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:

2.PNG1.PNG

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.

View solution in original post

5 REPLIES 5
sturlaws New Contributor
New Contributor

Re: Last month sum

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.

 

View solution in original post

Wresen Regular Visitor
Regular Visitor

Re: Last month sum

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 New Contributor
New Contributor

Re: Last month sum

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 )

 

v-jayw-msft Senior Member
Senior Member

Re: Last month sum

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:

2.PNG1.PNG

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.

View solution in original post

Highlighted
Wresen Regular Visitor
Regular Visitor

Re: Last month sum

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

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)