Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I come from an Excel user background & are trying to build my skills in Power BI & specifically Power Query M Code. My business have asked me to write a simple Power BI model to report graphically on KPIs. To achieve this I am trying to build a calendar table that will dynamically know when todays date is so it can calculate figures for last week, last period etc. I have searched the internet & found a number of people who have achieved this for standard calendar months, but not a system that can cope with the irregular pattern of 5+4+4 week quarters adjusted every few years to bring the year end back to the Saturday closest to 30 June, that is used by my company.
Therefore I have written the basic calendar table in Excel to link dates to financial years periods & weeks, & added uniquie index numbers for each. I have then imported it into Power Query & written some M code that knows todays date & can show if a specified date is past or future, & whether it is today. Up to that point it works fine. I then need some Mcode that will find the record where IsToday=Today & return the value in the Week Index colomn. This would be = the Current Week Index & deducting it from each Week Index for each record would give a week offset enabling the report to show this week, last week, 52 weeks ago etc. This would be a simple LOOK UP exercise in Excel but I am struggling to do it in M code.
The code I have written so far is
This inserts the Current Week Index field but shows an error
The error expands to
Have you any suggestions as to the M code I need to complete this look up?
Solved! Go to Solution.
Personal opinion/word of advice: Don't do it (in Power BI). Use an external dates/calendar table that you maintain in an Excel file on a sharepoint, or in a SQL Server table. That will not just save you lots of works, but also (hopefully) give you back some sanity. Especially with your off-standard fiscal calendar.
Personal opinion/word of advice: Don't do it (in Power BI). Use an external dates/calendar table that you maintain in an Excel file on a sharepoint, or in a SQL Server table. That will not just save you lots of works, but also (hopefully) give you back some sanity. Especially with your off-standard fiscal calendar.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.