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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MCarmassi
Frequent Visitor

Unexpected behaviour of Calculate with date filter

In my dataset I have the following data model. The relation between Header and Detail is through IdPurchaseInvoiceHeader while the relation between 'Tabella Date' and Header is between InvoiceDate and Date. 'Tabella Date' is defined as 

Tabella Date = distinct(SELECTCOLUMNS(PurchaseInvoiceHeader,"Date",PurchaseInvoiceHeader[InvoiceDate],"Year",[Year],"MonthNumber",[MonthNumber],"Month",[Month]))

image.png

Now in my report I have a slicer on 'Tabella Date'[Year] and a line chart where I want to show PurchaseInvoiceDetail[NetAmount] of the chosen year and the previous year by Month (which is 'Tabella Date'[Month]). I have a problem with the previous year.

 

I tried defining the measure as

@ Amount Previous Year = 
var Anno=[@ Previous Year]
return
if(Anno<>Selectedvalue(Param[MaxYear])-1,calculate(sum(PurchaseInvoiceDetail[NetAmount]),'Tabella Date'[Year]=Anno),calculate(sum(PurchaseInvoiceDetail[NetAmount],'Tabella Date'[Date]<=Date(Anno,Month(selectedvalue(Param[value])),Day(Selectedvalue(Param[Value]))) && 'Tabella Date'[Year]=Anno))

Here Param is a calculated table with a single row with value max('Tabella Date'[InvoiceDate]) and [@ Previous Year] is a measure that gives the year before the selected one. Now, if I choose the most recent year the result of this measure is wrong, giving for every month the total of the year instead of the amount for the specific month. I tried adding KEEPFILTERS and/or various ALL and ALLEXCEPT, but nothing changed.

If instead I chose an year other than the most recent one (so the if goes to the first calculate instead of the second one) everything works fine.

 

My interpretation is that the external context gives a filter on Year and Month. In calculate the filter on Year is overwritten by the new year, so we should have the intersection fo the filters on Month, on the Date and on the year, but this isn't how it works, it seems that the filter on Month is also overwritten. 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@MCarmassi what @lbendlin said, you can check related videos here on my channel:

 

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@MCarmassi what @lbendlin said, you can check related videos here on my channel:

 

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

lbendlin
Super User
Super User

That's not how you define date tables. Your calendar table has to be both contiguous (no gaps) and covering, including the result of the time intelligence function.

 

Use CALENDAR() or (worst case) CALENDARAUTO() for that.

Thank you. I corrected the calendar table to be contigous (using CALENDAR). I also understand now that adding a filter on the date of a calendar table removes all the other filter, so that is the reason I get the unexpected result.  

With a well defined calendar table I solved this specific issue using SAMEPERIODLASTYEAR,but I was wondering if is there a way to avoid this behaviour?

You want to let the data model do the work for you.  Only use filters if necessary.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.