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

[priya_rajendran]

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

[memepu · Accepted solution]

Hi @priya_rajendran ,

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.

[amitchandak]

@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])

[priya_rajendran]

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