The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Everybody,
I'm trying to build a measure which filters out values based on a year and retrieve the previous year. For example, if it's 2017, retrieve all 2016 fields. The problem I believe is with the filter. The Year field is a YYYY and the field Date is a M/D/YYYY hh:mm:ss. How do I get around this? Right now I am only retrieving a blank value. Here's my formula.
Current Year STLY =
var this_year = if(hasonevalue(Booking[Year]),
values(Booking[Year]), 2017)
var last_year = this_year - 1
var returnValue =
calculate(sum(Booking[Gross_Amount]),
filter (Booking, Booking[Date] = last_year &&
Booking[Year] = last_year)
)
return returnValue
Hi @erikboderek,
Could you please mark the proper answer as solution or share the answer if it's convenient for you? That will be a help to the others.
Best Regards!
Dale
Hey,
I would just build a separate Calendar table, join the date column from this table with your table, so that do not have to consider the year column in your booking com.
Then it will become much more simpler to build your previous year measures. Maybe this gets you started:
http://www.daxpatterns.com/time-patterns/
Hope this helps
I do have a calendar table and there is a relationship between that table and my Booking table, though not to that fields mentioned in my post. If I were to add a relationship between from calendar table to my year, and/or date field, what would this measure then look like?
The simplest form would be
CALCULATE(SUM('ResellerSales_USD'[SalesAmount_USD]), SAMEPERIODLASTYEAR('Calendar'[DateKey]))
But this makes it necessary that DateKey column from the Calendar table is related (and the relationship is active) with the Date column from your bookings table.
If it's not possible to join the columns I guess you have to go through the link from daxpatterns I mentioned above, almost everything is treated there.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |