In Part 1 we covered the setup for the Fiscal Comparison and is pre-requisite to this post.
In Part 2 focuses on the creation of the Date Range tables for our measures.
And here we are in Part 3, the final part in this series, here we create the measures that will use what we have created in the first two parts.
In this post we will create measures that will use the Date Range tables filtering information dynamically as time progresses and create some labels to indicate the days that have working days that have passed with regards to your sales.
Lets get started...
Previously we created the 'Today" and "Start of..." measures, now we will use these as the basis for additional measures that allow the creation of labels and additional filtering capabilities.
First up, "Days Gone" measures:
Without having the date tables we created in Part 2, attempting the calculation of working days in a given month is difficult and tedious and then on top of that trying to do it for the current month is even worse. They are possible of course, but having to do it for every measure is just messy and when you need to update anything, we, like anyone else, prefer to do it once and have the change apply everywhere where necessary.
The normal process for calculating these measures would be to calculate the number of working days in a given period or between a set of dates, which can result in some complex queries, but you will see the simple calculations we make below.
- Month Days Gone
.MonthDaysGone = CALCULATE(COUNTROWS(DATES_MTD), 'Calendar'[.IsWorkingDay] = 1)
Here we simply count the number of rows in the "DATES_MTD" table with a filter against the ".IsWorkingDay" column within the Calendar table. The same applies to the rest of the periods.
- Quarter Days Gone
.QuarterDaysGone = CALCULATE(COUNTROWS(DATES_QTD), 'Calendar'[.IsWorkingDay] = 1)
- Year Days Gone
.YearDaysGone = CALCULATE(COUNTROWS(DATES_YTD), 'Calendar'[.IsWorkingDay] = 1)
Second, "Days Gone %" measures"
Many users like the visual of percentages, so why not give it to them?
- Month Days Gone %
.MonthDaysGone% = DIVIDE(CALCULATE(COUNTROWS(DATES_MTD), 'Calendar'[.IsWorkingDay] = 1), CALCULATE(COUNTROWS(DATES_MTH), 'Calendar'[.IsWorkingDay] = 1))
- Quarter Days Gone %
.QuarterDaysGone% = DIVIDE(CALCULATE(COUNTROWS(DATES_QTD), 'Calendar'[.IsWorkingDay] = 1), CALCULATE(COUNTROWS(DATES_QTR), 'Calendar'[.IsWorkingDay] = 1))
- Year Days Gone %
.YearDaysGone% = DIVIDE(CALCULATE(COUNTROWS(DATES_YTD), 'Calendar'[.IsWorkingDay] = 1), CALCULATE(COUNTROWS(DATES_YR), 'Calendar'[.IsWorkingDay] = 1))
Here we divide the counts of the "To Date" and "Full" periods and get a percentage.
REMEMBER: To set the data types of measures you create, particularly percentage values.
Again this is dynamic and simply... set and forget :)
Finally, top of the measures to the Calendar table, lets and a few user friendly labels based on the above:
- Month Days Gone label:
.Label.MTD.DaysGone = "MTD - Days Gone: " & [.MonthDaysGone] & " (" & FORMAT([.MonthDaysGone%], "0%") & ")"
- Quarter Days Gone label:
.Label.QTD.DaysGone = "QTD - Days Gone: " & [.QuarterDaysGone] & " (" & FORMAT([.QuarterDaysGone%], "0%") & ")"
- Year Days Gone label:
.Label.YTD.DaysGone = "YTD - Days Gone: " & [.YearDaysGone] & " (" & FORMAT([.YearDaysGone%], "0%") & ")"
Result of above should look something like this:
Sales Data Measures and Filters
By know you should have a really good understand of the capabilities and the benefits of the Dates tables, but as a brief example of how to filter your sales data for example, we have included a few examples of what you can do.
Please note that these are crude examples and you will need to update accordingly.
- Sales for the Month To Data:
Sales: MTD = CALCULATE(SUM('SalesTable'[ValueColumm]), DATES_MTD[Date])
- Percentage of Sales against the Budget for the Quarter
Sales: Fiscal Quarter % to Budget =
CALCULATE(DIVIDE(SUM('SalesTable'[value]), SUM('SalesTable'[budgetvalue])), DATES_QTR[Date])
- Sales for current month period vs the sales achieved in the same month Last Year.
Sales: Full Month % to Last Year =
DIVIDE(CALCULATE(SUM('SalesTable'[value]), DATES_MTD[Date]), CALCULATE(SUM('SalesTable'[value]), DATES_MTH_LY[Date]))
There are many more that you can create and by using those dynamic tables.
And that's a wrap...
This was a short post but it does provide a big launch pad to generate some detailed reporting which will no doubt impress those that need to be in the know about the status of the business and its performance.
We hope that the information provided in this series helps many who followed it to identify additional measures and capabilities of Power BI.
Please comment and let us know what you would like to see next.
Till the next post/series, have fun with Power BI!