topic Re: Calculate current month & previous month values in DAX Commands and Tips

Calculate current month & previous month values

blowers — Wed, 15 Nov 2023 15:57:46 GMT

I have created the following calcs for current & previous months counts when joining to a date table, however they dont account for the year which is causing me issues. Is there a way to add year to these existing calcs?

My aim is to get current & previous months counts and then use a day field (1-31) to display these in a graph for comparison.

Current Month = CALCULATE(count(Table1[column]),FILTER('Date Table',MONTH('Date Table'[Date])=MONTH(now())))

Previous Month = var current_month= MONTH(TODAY()) return
CALCULATE(COUNT(Table1[column]),FILTER('Date Table',MONTH('Date Table'[Date])=current_month -1))

Thanks!

Re: Calculate current month & previous month values

ERD — Wed, 15 Nov 2023 16:50:51 GMT

Hi blowers ,

So for the understanding of this date, previous month in a way you did it:

dates = VAR _today = TODAY () VAR _year = YEAR ( _today ) VAR _month = MONTH ( _today ) VAR _previousMonth = _month - 1 RETURN "This month-year: " & _year & " " & _month & " " & "Previous month-year: " & _year & " " & _previousMonth

Your date table can and I believe should contain columns like Month, Year, etc. It is easy to build in Power Query, DAX, etc. So you'll have something like this:

Current Month = VAR _today = TODAY () VAR _year = YEAR ( _today ) VAR _month = MONTH ( _today ) RETURN CALCULATE ( COUNT ( Table1[column] ), 'Date Table'[Month] = _month, 'Date Table'[Year] = _year )

Re: Calculate current month & previous month values

blowers — Tue, 21 Nov 2023 09:15:02 GMT

I get an error when using this calculation, DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

I do have a date table in my model, month is populated as Jul, Aug, Sep etc, and year is populated as 2021, 2022, 2023 etc. Any ideas where im going wrong?

Re: Calculate current month & previous month values

FreemanZ — Tue, 21 Nov 2023 09:26:46 GMT

hi blowers ,

what does this mean: "use a day field (1-31) to display these in a graph for comparison."?

Re: Calculate current month & previous month values

blowers — Tue, 21 Nov 2023 09:51:25 GMT

So the graph should have the days of the month, and then the current month and previous month counts over that monthly period. I keep having issues as it keeps counting the previous year in the graph calulations.

Re: Calculate current month & previous month values

blowers — Wed, 22 Nov 2023 11:01:26 GMT

I was overthinking this 😀. Used these calcs and then applied slicers for month & year on the graph

Current Month Year = CALCULATE(

SUM (table[count])

Previous Month Year =

CALCULATE(

SUM (table[count])

SAMEPERIODLASTYEAR('Date Table'[Date]))