problem with measure

[emma313823]

I have pulled in some tables from an actively used Access database which has one table reporting on values of checks which we receive. I want to create two measures from this table which I can use on cards which will show a total for the previous month and a total for the current month.

I do have a MASTER date table called Calendar_Dates and have created a relationship from this master date table to the tblIncomingCommissions Pay-Date.

I tried the following DAX for the current month, but it's telling me I have to many arguments.

CurrentMoComm = CALCULATE(SUM('tblIncomingCommissions'[Check_Value],

FILTER('Calendar_Dates',MONTH(Calendar_Dates[Date]) = MONTH(TODAY())),

FILTER('Calendar_Dates',YEAR(Calendar_Dates[Date] = YEAR(TODAY())))))

Could someone help me figure this out or if there is better way to achieve current and previous? 

I really like new card visual, where I can stack and would like to have these two measure to be stacked in the 'new card' visual.

[NaveenGandhi]

Hi @emma313823 

please try below DAX.

CurrentMoComm =
CALCULATE(
SUM('tblIncomingCommissions'[Check_Value]),
FILTER(
'Calendar_Dates',
MONTH('Calendar_Dates'[Date]) = MONTH(TODAY()) &&
YEAR('Calendar_Dates'[Date]) = YEAR(TODAY())
)
)


If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!

[emma313823]

Could you help me a bit understanding what I did wrong?

[NaveenGandhi · Accepted solution]

@emma313823 

Filter accepts only one argument, If you need to provide 2 arguments(month and year) you need to use &&.

Also for previous month use below dax.

Prevmonth =  =
CALCULATE(
SUM('tblIncomingCommissions'[Check_Value]),
FILTER(
'Calendar_Dates',
MONTH('Calendar_Dates'[Date]) = MONTH(TODAY())-1 &&
YEAR('Calendar_Dates'[Date]) = YEAR(TODAY())
)
)

But this will remain static(change each month automatically), But if you want the user to select what the current month is. Then create two slicers for year and month. When use select a year and a month the below dax will give current and previous month.

CM = SUM('tblIncomingCommissions'[Check_Value])

Previous month = calculate(CM, 

PREVIOUSMONTH(datecolumn))

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!