Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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 @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
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |