cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## How to get the current month's value in the grand total of a measure

I have a measure that gives its max value in the grand total.

units = IF(ISFILTERED(calendar_table[Month]),[unit],MAXX(VALUES(calendar_table[Month]),[unit]))

My requirement is to now to get the current month's value in the grand total instead of the max value.
Any idea how that could be done ?
snapshot attached. To get 10857(current month's value) in total instead of 10875(which is the max value).
1 ACCEPTED SOLUTION
Resolver II

I have created a simple smaple, please refer to it to see if it helps you.

How to get the current month's value in the grand total of a measure.pbix

Create a column about month first.

``month = MONTH('Table'[date])``

Then Create a measure.

``````Measure =
VAR _monthtoday =
MONTH ( TODAY () )
VAR _value =
CALCULATE (
MAX ( 'Table'[units] ),
FILTER ( ALL ( 'Table' ), 'Table'[month] = _monthtoday )
)
RETURN
IF ( ISINSCOPE ( 'Table'[date] ), MAX ( 'Table'[units] ), _value )``````

If I have misunderstood your meaning, you can create simple data with excel, then show me the screenshots about the data and the desired output your want.

Best regards.

3 REPLIES 3
Resolver II

I have created a simple smaple, please refer to it to see if it helps you.

How to get the current month's value in the grand total of a measure.pbix

Create a column about month first.

``month = MONTH('Table'[date])``

Then Create a measure.

``````Measure =
VAR _monthtoday =
MONTH ( TODAY () )
VAR _value =
CALCULATE (
MAX ( 'Table'[units] ),
FILTER ( ALL ( 'Table' ), 'Table'[month] = _monthtoday )
)
RETURN
IF ( ISINSCOPE ( 'Table'[date] ), MAX ( 'Table'[units] ), _value )``````

If I have misunderstood your meaning, you can create simple data with excel, then show me the screenshots about the data and the desired output your want.

Best regards.

Super User

@priya_rajendran , Use datesmtd, that will do that

calculate([unit], datemtd('Date'[Date])

or

Measure =

var _max = maxx(allselected(Table), Table[Date])

var _min = eomonth(_max,-1)+1 ,

return

if(eomonth(_max,0) =eomonth( max(Date[Date]),0)  ,CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max)), [Unit])

Frequent Visitor

Hi @amitchandak
Thanks for your reply. I am now able to get the current months's value in the total, but that value is getting reflected for the last month also. I want all the months to pick [unit] and only the total value should pick the current  month's value.

Measure i used :

var current_mth_nbr = CALCULATE(min(calendar_table[SortYearMonth]),calendar_table[Date]=TODAY())
var current_mth_nbr_min_date = CALCULATE(min(calendar_table[Date]),calendar_table[SortYearMonth]=current_mth_nbr,REMOVEFILTERS(calendar_table))
var current_mth_nbr_max_date = CALCULATE(max(calendar_table[Date]),calendar_table[SortYearMonth]=current_mth_nbr,REMOVEFILTERS(calendar_table))
var _max = maxx(ALLSELECTED(calendar_table),calendar_table[Date])
var _min = EOMONTH(_max,-1)+1
return IF(EOMONTH(_max,0) = EOMONTH(MAX(calendar_table[Date]),0),CALCULATE([Unit],DATESBETWEEN(calendar_table[Date],current_mth_nbr_min_date,current_mth_nbr_max_date)),[Unit])