Does behavior of a DAX measure change over time?

[imadorkous]

So in 2020 I had set up a DAX measure to sum up Sales quota target that was YTD to the current month.  The formula was:

Sales Quotas.Quota Value (YTD) = VAR _YTD =

CALCULATE (

SUM('Sales Quotas'[EOMTarget]),

FILTER (

ALL ( 'Date' ),

'Date'[Date.Year] = MAX ( 'Date'[Date.Year] )

&& 'Date'[Date.Index] <= MAX ( 'Date'[Date.Index] )

)

,FILTER('Territory', IFERROR(SEARCH(Username(),'Territory'[.Users]), BLANK())>0)

)

RETURN

_YTD

Where the filter on the visual was Date.Months from today = to -1, so I can get the  summation of the Sales quota year to date, from beginning of the year to the previous month. I know this calculation is not how it should be typically be written, however it worked back in 2020, but now I am noticing the calculation is not working any more, and it shows the total Sales quota for the whole year. 

 

So to do some testing, I wanted to isolate to the date table and do a calculation is working as it supposed to be from 2020 to 2022. So I created the following measure:

Max Month Test =

CALCULATE (

Max('Date'[Date.Month]),

FILTER (

ALL ( 'Date' ),

'Date'[Date.Year] = MAX ( 'Date'[Date.Year] )

&& 'Date'[Date.Index] <= MAX ( 'Date'[Date.Index] )

)

)

So I get the following result in my 2020 file (saved in Sept 2020):

The filter on the visual is:

This is value is correct, and it working as it should with the Date.Months from Today filter of -1. I just want the max month of August, Month 8

The same formula in my current 2022 file (March 2022) I get the following result:

The filter on the visual is the same:

This result is wrong and it should be 2 if I use the same logic that I applied from my first file, since the current month is March, that would be 3, and with the Date.Months from Today = -1 it should bring me 2. Is there something that I am missing?? All my relationships from my tables are the same and date table hasnt changed. So I don't know what is going on? Does anyone know?

I also attached my date tables from 2020 and 2022 as well:

2020 Date Table:

https://docs.google.com/spreadsheets/d/1Wzx35plCy2U09lWPg0pvWJnLvvJzyzw6/edit?usp=sharing&ouid=10425...

2022 Date Table:

https://docs.google.com/spreadsheets/d/19fAhLnstJ_BhrUfJXTYmh5Cu7mPRH1f4/edit?usp=sharing&ouid=10425...

[Anonymous · Accepted solution]

Hi @imadorkous ,

You could create a measure like below.

Measure 4 = DATEDIFF(TODAY(),SELECTEDVALUE('Table (2)'[Column1]),MONTH)

Then add this measure to visual filter set value = -1.

Best Regards,

Jay