Measure not working

[UserNW16AA]

Hello

Request your valuable input to understand why the below measure does not return any values

GrossFeesPYTD =

VAR LatestDateCurrentYear = max(BillByCRDS[MNTH])

 VAR LatestDateLastYear = date(YEAR(LatestDatecurrentYear)-1,Month(LatestDatecurrentYear),DAY(LatestDatecurrentYear))

 Var FirstDateLastYear = date(Year(LatestDateCurrentYear)-1, 1, 1)

 RETURN

 CALCULATE([GrossFees],

    FILTER(ALL(BillByCRDS[MNTH]),BillByCRDS[MNTH]>= FirstDateLastYear && BillByCRDS[MNTH] <= LatestDateLastYear))

where as if I remove -1 (included in var LatestDateLastYear), then the measure works except it produces the results for current year

thanks for your help

[UserNW16AA]

thanks Sam & Jian for your help

[Anonymous · Accepted solution]

Hi,@SamWiseOwl ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@UserNW16AA I am glad to help you.

According to your description, when you get the previous year's parameter and use it as a filter, the measure doesn't return any value
This is very strange because after my testing, your code doesn't seem to have any syntax issues
You can try returning the values of the variables LatestDateCurrentYear,LatestDateLastYear,FirstDateLastYear individually to see if the values of these variables are correct.
I created test data to run the measure you provided.
Your [GrossFees] should be another measure, here I used a simple max as a replacement

As you can see I output the variable values separately and GrossFeesPYTD also calculates the current expected result (returns the value of CrossFees for the previous year for the current row).
So I think it's possible that your [GrossFees] doesn't have a value that is satisfied by the current date filter, and that's why CrossFeesPYTD calculates a null result, so you can check your measure to see if there is really a value for it.

Of course, the calculation environment is also very important, in fact, the calculation result of GrossFeesPYTD is very dependent on the calculation result of GrossFees (corresponding to [measure] in my test data)
As you can see, if I change the calculation logic of [measure] and change MAX to SUM, GrossFeesPYTD even shows the effect of accumulation.

I think that the GrossFeesPYTD measure is actually an enhanced version of the GrossFees in your code (you just added this filter for the previous year)
So I think you need to check that the GrossFees measure is calculated in the correct environment and code, and that there are no other external filters affecting it, and that GrossFees has the correct value for the previous year and is not empty.
If you have checked all these things and confirmed that there is no problem, then we go back to your original question: LatestDateLastYear.
You can try to use the following definition (actually I don't think this is the main reason, the main reason is probably related to GrossFees).

GrossFeesPYTD_DateADD = 
VAR LatestDateCurrentYear = max(BillByCRDS[MNTH])
 VAR LatestDateLastYear = MAXX('BillByCRDS',DATEADD('BillByCRDS'[MNTH],-1,YEAR))
 Var FirstDateLastYear = date(Year(LatestDateCurrentYear)-1, 1, 1)
 RETURN
 CALCULATE([Measure],
    FILTER(ALL(BillByCRDS[MNTH]),BillByCRDS[MNTH]>= FirstDateLastYear && BillByCRDS[MNTH] <= LatestDateLastYear))

Ensure that the GrossFees metric has the correct value under the current date filter condition. If GrossFees does not return any values under the filter condition, then GrossFeesPYTD will also return null values.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SamWiseOwl]

What if you change the LatestDateLastYear to DateAdd(lastdate(BillByCRDS[MNTH]), -1, year) ?

[UserNW16AA]

Then I get the error message "The first argument to 'DATEADD' must specify a column."

[SamWiseOwl]

I would consider changing this:

 CALCULATE([GrossFees],

    FILTER(ALL(BillByCRDS[MNTH]),BillByCRDS[MNTH]>= FirstDateLastYear && BillByCRDS[MNTH] <= LatestDateLastYear))

to

CALCULATE([GrossFees], DatesBetween(BillByCRDS[MNTH], FirstDateLastYear, LatestDateLastYear)

Have you tried returning each of your variables to check they return the dates you think?

[UserNW16AA]

thanks Sam

I used the piece of code you provided still no result was generated

I  did try running for each of the vairables. I get the result for var LatestDateCurrentYear & for var 

FirstDateLastYear, however variable LatestDateLastYear did not produce any results