Forum Discussion
Solved
calculation using current month
Hi All, Is there a way to calculate a simple measure like, total count of something (filings) but in a way that it dynamically updates with the current month of the data. For example, I have this...
Hi Greg, Greg_Deckler I am having success with this:
Value on Last Year/Month =CALCULATE(COUNT( 'JBSIS Probate Filings'[Case Number] ),FILTER( 'JBSIS Probate Filings', 'JBSIS Probate Filings'[Report Year Month] = MAX( 'JBSIS Probate Filings'[Report Year Month] ) ))
pfarahani So generally, the way this is done is to create a calculated column like the following:
Column IsCurrentMonth =
VAR __Today = TODAY()
VAR __CurrentYear = YEAR(__Today)
VAR __CurrentMonth = MONTH(__Today)
VAR __Year = YEAR('Table'[Date])
VAR __Month = MONTH('Table'[Date])
RETURN
IF(__Year = __CurrentYear && __Month = __CurrentMonth,1,0)
Column IsCurrentMonth Alternate =
VAR __CurrentEOMonth = EOMONTH(TODAY(),0)
VAR __EoMonth = EOMONTH([Date],0)
RETURN
IF __EoMonth = __CurrentEoMonth, 1, 0)
Then you can filter your visual on this column = 1
Hi Greg, Greg_Deckler - my bad, I looked more closely at the data in this dashboard, its a little but different. There is this filings table, I am trying to create a measure to count total filings based on the last/max month-year in this dataset, which right now is December of 2019
pfarahani OK, how about this one:
Column IsCurrentMonth = VAR __MaxYear = MAX('Table'[Report Year]) VAR __MaxMonth = MAXX(FILTER('Table',[Report Year] = __MaxYear),[Report Month]) RETURN IF([Report Year]=__MaxYear && [Report Month]=__MaxMonth,1,0)Hi Greg, Greg_Deckler I am having success with this:
Value on Last Year/Month =CALCULATE(COUNT( 'JBSIS Probate Filings'[Case Number] ),FILTER( 'JBSIS Probate Filings', 'JBSIS Probate Filings'[Report Year Month] = MAX( 'JBSIS Probate Filings'[Report Year Month] ) ))pfarahani Yep, I can see how that would work too!