Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JoeAPH
Regular Visitor

Introducing Month field from Calendar changes YTD calculation

I have a measure
 
 
Sales YTD = CALCULATE([Ttl Sales],
FILTER(trans,year(trans[DATE])=MAX(Calendar2[Year])),
FILTER(trans,trans[Hold?]="n"))
 
Note: Ttl Sales = Sum(trans[NetSales])
 
In my chart this measure correctly computes as expected ie gives me the total Net Sales in the 'trans' table for transactions dated in the current year (which is the max year in my Calendar2 table
 
So all is well.
 
However, when I introduce the Month field (which is an integer in my Calendar2 table), the chart shows the Net Sales per year for the year to date, but then shows Net Sales for last year for the remaining Months.
 
Can anyone explain this please?
 
 
 
1 ACCEPTED SOLUTION

Hi

 

I added the RETURN command, and it is working perfectly!

 

Many thanks

 

Joe

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

@JoeAPH - Does your calendar table include future dates?

 Hi

 

Thank you for your time!

 

No. Calendar cust off at current day.

 

Joe

Anonymous
Not applicable

Then let's say you have a filter to limit to only July. Then your calculation is run in that context, thus finding the max year associated with July: 2018. 

If you want the max year from the calendar table, and ignore filters, you can do something like:

Sales YTD = 
var max_year = CALCULATE(MAX(Calendar2[Year]), ALL(Calendar2))
return CALCULATE([Ttl Sales],  //***Edit: Had forgotten to include "return"// 
FILTER(trans,year(trans[DATE])=max_year),
FILTER(trans,trans[Hold?]="n"))

Note: this is not a typical YTD function, as YTD usually refers to the cumulative value each month.

 

Hi

 

Thank you for your explanation of the error and formula suggestion.

 

I'm getting this error:

 

The syntax for 'CALCULATE' is incorrect. (DAX(var max_year = CALCULATE(MAX(Calendar2[Year]), ALL(Calendar2))CALCULATE([Ttl Sales],FILTER(trans,year(trans[DATE])=max_year),FILTER(trans,trans[Hold?]="n")))).

 

Any ideas?

Hi

 

I added the RETURN command, and it is working perfectly!

 

Many thanks

 

Joe

cuts*

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.