topic Re: Measure that uses Calculate and filter on year gives same value for every month in table in DAX Commands and Tips

Measure that uses Calculate and filter on year gives same value for every month in table

JoyceW — Wed, 09 Feb 2022 15:49:24 GMT

I have a measure that does a calculate of a sum of revenue in a sales table on filtered ledger accounts.

I use a date table with date, quarter and year. The date has a relationship with the date of sales of the salestable.

Then I created a second measure, that calculate's the first measure on the year in the datetable:

Omzet HJ = CALCULATE( [Omzet], FILTER(ALL(Datumtabel), Datumtabel[Jaar] = YEAR(TODAY()))

When I add this measure in a table with month and year it shows the same value every month. When adding the [Omzet] measure it does calculate the value per month / year. So why doesn't it work with the [Omzet HJ] measure?

The ultimate goal is creating a table with per month: revenue last year / revenue last year + 10% (as the target) / revenue current year / difference between last year + 10% and current year. And then those values per month and quarter. It will be used as a sales target dashboard so each sales manager can see their target and whether they have reached it or not.

Any help, tips or solutions are very welcome. Thank you!

Re: Measure that uses Calculate and filter on year gives same value for every month in table

ValtteriN — Wed, 09 Feb 2022 15:58:59 GMT

Hi,

I am not sure if I follow but you want to have the calculation on a monthly basis? Now your ALL removes all the filters from your datetable. Afterwards you specify that the year in you calculation needs to be this year, but you don't define the month filter context back. Try this: ALL(Datumtabel[jaar]). This will only remove the year context. For LY you can use YEAR(TODAY())-1 in the measure. One alternative way to do this is to add month selection to your FILTER e.g. Datumtable[Maand]=MAX(Datumtable[Maand]).

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Measure that uses Calculate and filter on year gives same value for every month in table

JoyceW — Wed, 09 Feb 2022 16:24:33 GMT

Hi ValtteriN , thank you! adding the Maand to the filter worked like a charm!