Forum Discussion
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
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.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.
5 Replies
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.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
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 )
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.
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
