Calculate current month & previous month values

[blowers]

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!

[blowers · Accepted solution]

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

[ERD]

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 )

[blowers]

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?

[FreemanZ]

hi @blowers ,

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

[blowers]

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.