Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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]))
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.
Solved! Go to Solution.
@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.
@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.
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.
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |