cancel
Showing results for
Did you mean: 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])

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines 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]) Announcements #### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023. #### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers! #### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture. Top Solution Authors
Top Kudoed Authors
Users online (2,536)