Hi Graham that looks great! That's very similar to the solution we came up with as well. In our Foundations report, we declare a variable to represent the fiscal start month (this would be the calendar month, which in your case would be 7):
Original Message:
Sent: 09-27-2023 22:02
From: Graham Walters
Subject: Scaling Power BI Adoption
This intrigued me, as when I started integrating vena with PowerBi, I struggled to get a calendar setup with all the required parameters for a financial year 1 Jul to 30 Jun. I built a power query table which is complex but works and I use as necessary. Reading this thread prompted me to relook at this. With the help of ChatGPT, the following simple DAX command will set up a Financial Year table.
Calendar =
VAR StartDate = DATE(2010, 7, 1)
VAR EndDate = DATE(YEAR(TODAY()) + 5, MONTH(TODAY()), DAY(TODAY()))
VAR CalendarTable =
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"FinYear",
"FY" & MID(format([Date], "YYYY"), 3, 2),
"FinMth", FORMAT(MONTH([Date]) - 6, "00"),
"MMM", FORMAT([Date], "MMM"),
"FinQtr", IF(MONTH([Date]) >= 7, "Q" & format(INT((MONTH([Date]) - 7) / 3) + 1, "0"), "Q" & FORMAT(INT((MONTH([Date]) + 5) / 3) + 1, "0")),
"FinHY", IF(MONTH([Date]) >= 7, "H1", "H2")
)
RETURN
CalendarTable
------------------------------
Graham Walters
CFO
Jason Windows
Original Message:
Sent: 09-26-2023 09:21
From: Anton Medvedev
Subject: Scaling Power BI Adoption
It's a really common use case, the way we've accounted for this in the Foundations dashboard is by adjusting the Fiscal Date generated by the CALENDAR() function by the fiscal start month. Let's say your fiscal start month is March, we would adjust the Fiscal Date by 2 months and create a new column, from there you can parse that new column into Fiscal Month and Fiscal Year, very similar to the logic for parsing calendar months and years.
#ExpertExchange
------------------------------
Anton Medvedev
Consultant
Vena Solutions
Original Message:
Sent: 09-25-2023 13:53
From: Glen Camden Camden
Subject: Scaling Power BI Adoption
Love this! Thanks @Anton Medvedev
What about the fiscal year, any thoughts on how to set up a good date table schema there? I always created multiple columns to realign the dates to a non-calendar year fiscal. I then found some time series visualizations a bit more challenging to execute on without different leverage a custom sort field.
#ExpertExchange
------------------------------
Glen Camden Camden
Vena Solutions
Original Message:
Sent: 09-25-2023 12:40
From: Anton Medvedev
Subject: Scaling Power BI Adoption
Hi Glen,
That's a great question, you always want to make your Power BI schema as flexible as possible, one trick is to use the a combination of MIN(), MAX(), and DATE() something like:
CALENDAR(
CalendarStart = DATE(MIN('values'[Year']), 1, 1),
CalendarEnd = EOMONTH(DATE(MAX('values'[Year']), 12, 1),0)
)
In this example, the Year column in the values table contains our years. If that column gets updated with more observations, a refresh will pick up the changes and update the output of this DAX logic.
#ExpertExchange @Glen Camden Camden
------------------------------
Anton Medvedev
Consultant
Vena Solutions
Original Message:
Sent: 09-25-2023 12:27
From: Glen Camden Camden
Subject: Scaling Power BI Adoption
So excited for this series @Anton Medvedev!
Personally, I love the having dynamic end dates for the Calendar function!
VAR CurrentYear = YEAR(TODAY()) // Identify current year
VAR EndYear = CurrentYear + 3 // Add 3 years to the current year
VAR EndDate = DATE(EndYear, 12, 31) // Setting the end date to December 31 of the end year
VAR CalendarVar = CALENDAR(DATE(1990, 1, 1), EndDate) // Apply EndDate VAR to the CalendarVar so the date table becomes dynamic
RETURN
ADDCOLUMNS(
CalendarVar,
"Fiscal Year", YEAR([Date]),
"Fiscal Period", MONTH([Date]),
"Fiscal Quarter", ROUNDUP(MONTH([Date])/3,0)
)
What are your thoughts on this approach?
What would your recommendation be for those who may not have a calendar fiscal year setting up their date table?
#ExpertExchange
------------------------------
Glen Camden Camden
Vena Solutions
Original Message:
Sent: 09-25-2023 08:34
From: Anton Medvedev
Subject: Scaling Power BI Adoption
I'm Anton, a Product Manager on the Data Insights team here at Vena. I've been at Vena for 4 years, working as a consultant followed by some solution management work before taking on my current role. Before my time at Vena, I worked in corporate finance, specializing in FP&A and predictive forecasting.
I am so excited to host the first edition of the #ExpertExchange, where every week industry experts will join our forum to share knowledge, answer questions and help you grow your Vena skills.
Since my focus at Vena is on data intelligence, this week's #ExpertExchange will be about enabling Power BI adoption across your organization. If you have questions or comments about Power BI and Vena, please drop them in the thread. I'm looking forward to connecting, learning and growing with you all!
Power BI is a powerful tool for gaining insights at scale. Using the analogy of a construction site to represent analytical processes within an organization, I like to think of Excel as the shovel. It's the trusty tool you can always turn to for ad-hoc querying. And Power BI is the powerful excavator, the piece of equipment that really gets the job done. With great power comes complexity, so here are a few tips for building #PowerBI reports that are intuitive for anyone to use.
Use slicers for variance analysis.
Variance analysis remains the bedrock of how we evaluate our businesses. Being able to instantaneously calculate year-over-year growth or variances to budget using different slices of our data provides us with the insights we need to make intelligent decisions. It is simply not enough to know that sales are favourable to budget by 5%, we need to know WHY! That's where we, as finance people, rely on our business stakeholders to help us advance our understanding. To enable these stakeholders, we need to ensure our tools are intuitive for everyone. One stellar example of how we can make analytics tools, specifically variance analysis, available to all users throughout our organization is through the use of slicers. Slicers are visuals that act as filters for the other visuals on the page.
Here is a quick guide on how these can be set up to scale your analytics:
Create a slicer visual with one of the columns from your dataset that will be used to filter visuals.
Create a DAX expression that looks to the slicer to take what users select and apply those selections as filters to the values you would like to visualize. In our Foundation report, we use this logic to filter our numerical values (Rollup Value) by our Scenario dimension, which is the column used in the slicer. Here is the expression: CALCULATE('values'[Rollup Value], 'values'[Scenario] = SELECTEDVALUE('Base Scenario Filter'[Scenario])).
Note in this case the ('Base Scenario Filter'[Scenario])
is holding the column used in the slicer.
Use the output of the DAX expression in a visual of your choosing, whether it be a line graph, bar graph, your choice!
Voila, now your users can quickly slice visuals to draw out insights without needing to manually drag and drop columns into the Filter pane.
Data types are a powerful feature in the Microsoft ecosystem, especially when it comes to dates. Using the Date data type allows users to perform time-intelligent calculations such as year-over-year growth. Additionally, this data type allows users to drill into any time axis, seamlessly going from an annualized view of your data, to semi-annual, quarterly, etc.! Best practice states we should create a table to hold our calendar logic; this ensures that Year, Month, any other time context has its' own column, so when you apply this table in filtering logic it stays cohesive. The steps to accomplish this are:
Create a table with 1 date column such as CALENDAR(DATE(1990, 1, 1), DATE(2030, 12, 31)
)
encompassing the date range you deem relevant for your dataset.
Create additional columns to pull out whichever time construct from that date, such as year, month, etc.
Use any column from this table in a visual or filter.
Here is a simple table for pulling out Years, Months, and Quarters:
VAR CalendarVar = CALENDAR(DATE(1990, 1, 1), DATE(2030, 12, 31))
"Fiscal Year", YEAR([Date]),
"Fiscal Period", MONTH([Date]),
"Fiscal Quarter", ROUNDUP(MONTH([Date])/3,0)
You can now use these columns in slicers, visuals, and other DAX calculations to perform intelligent time calculations.
What other tips would you share with the community to help us all level up together?
------------------------------
Anton Medvedev
Product Manager
Vena Solutions
------------------------------