topic Re: Hi! Challenging Problem with PreviousMonth() using a measure as a filter in DAX Commands and Tips

Hi! Challenging Problem with PreviousMonth() using a measure as a filter

PowerBIUser1995 — Fri, 15 May 2020 05:33:15 GMT

Hi everyone!,

I am trying to calculate the following but I just obtain a blank space: It is defined the following:

==> These measures works fine:

SalesLastMonth:=calculate([Sales],PREVIOUSMONTH(Calendar[Day]))
Incentive_AchievementRate:=divide(calculate([Sales],filter(Target_Table,Target_Table[Target]>0)),sumx(Target_Table,Target_Table[Target]))

==>BUT This doesn't work!!:

SalesOfWinners_LastMonth:=calculate([SalesLastMonth],filter(Sales_All,[Incentive_AchievementRate]>1))

What I need to know is the sales of the last month, but only of the ones that won the incentive this month. The structure works when I use as a filter a column that exists in a Table instead of a measure (as [Incentive_AchievementRate])

What can I do?

Thanks!

Re: Hi! Challenging Problem with PreviousMonth() using a measure as a filter

mahoneypat — Fri, 15 May 2020 11:38:41 GMT

It's hard to tell without seeing your model, but it looks like you are filtering the whole fact table - Filter(Sales_All), ...

What field/column do you calculate incentive winners on usually (employees, departments, etc.)? I would have expected you had Filter(All(Sales_All[Employee]), [Incentive_AchievementRate]>1) instead.

If this solution works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Hi! Challenging Problem with PreviousMonth() using a measure as a filter

PowerBIUser1995 — Fri, 15 May 2020 17:16:53 GMT

Indeed, the problem was the table. Thanks!