Storms in Pursuit

Racing across I-70 towards St. Louis, the thought of exiting, on one of many two-lane routes leading away from that travel chaos, crossed our mind more than once. Storms rode our tailgate throughout the day though, recommending otherwise. There would be time for the two-lane, when it was required.

Tornadic conditions were promised in the region in which my comrade and I planned for first base camp. The first warnings were dispatched just as the dying remnants of Route 66 faded into the rear-view mirror. Blue sky opened in spite of the warnings, with two-lane stretched out in front of our transport, finally offering a chance to pause at random amusements, here and there.

One of the spots enabled an opportunity to have our transport swallowed by the earth. An alignment too close to the edge of the exit portal caused the vehicle to be momentarily captured in the maw of a drainage serpent. Narrowly escaping with our lives, the remainder of the journey remained mostly uneventful.

Old Copper Mines Schoolhouse greeted us in no time, standing lonely under the blue sky that was quickly clouding over. Scant information is known about the place; actually, nothing beyond the name and location. A little further down and around the bend our first base camp offered safety from road hazards and the coming weather. It appeared as though it was still a few hours behind us though.

A desire for sustenance demanded the expedition risk one more crossing into the elements. A nearby, recently rehabilitated train depot enabled the activity to proceed quickly enough for return to base camp prior to clouds of doom unleashing their fury. Warnings continued to flash across the air waves. A bit further down the road, the little town of Wynne in Arkansas began to think about rebuilding. Check-in calls abounded with the news. We assured all that the incident 300 km away from our locale had left us mostly unscathed.

The storm finally rolled in much later than expected. We were beginning to wonder if it would come at all. Ample warning from the wall of rain, heard miles away, racing towards us from across the valley, enabled adequate time for retreat to our shelter. It poured copiously for about an hour, then ceased abruptly, leaving only occasional flashes of lightning and an ominous sky void of any starlight. That is as bad as circumstances got, or we slept through it. The weather stays up entirely too late sometimes.

Sequencing Months and Years in Power BI

Sequencing months within Power BI can be a bit awkward, if you need to constantly shift months to show a rolling 12 or however many, while continually keeping them in the order in which they fall across years.

Solutions of the complex variety abound. Simple answers are rare, likely because most of the folks writing them have been mired in code for entirely too long. That is not to say they are wrong, just that there appears a tendency towards over-programming.

A simple solution only requires a little bit of math to produce results perfectly adapted to ordering months and their associated years in visualizations.

It can be done with or without a calendar dataset; either way, a date field is required.

In Power Query, add a [Year] and [Month] columns, both of the numeric variety.

The next step is to simply add a Custom Column with the following, which is M language, indicating to take the date and convert it to text and concatenate the two.

= Text.Combine({Date.ToText([#”Date”], “yyyy”), “ “, Date.ToText([#”Date”], “MMM”)})

The output in the new column is “2023 Mar” for March 2023.

Lastly, create one more Custom Column with the following code (M again).

=[Year]*100+[Month]

The output in the new column is “202303” for March 2023.

Mission accomplished, almost.

Back in Power BI, sort one column by the other and all months from here into eternity are sorted correctly by year and month.

Compare that with a DAX solution captured from somewhere a few months ago, hailed in some parts of the interweb as the greatest thing since sliced bread. It works just as well, relying on the same columns, but for non-programmer types is not quite as easy to wrap your head around, much less remember it over and over again.

Seq Month =

VAR MaxMos = SUMMARIZE(ALL(‘Calendar’),’A Calendar’[Year],”MaxMonth”,MAX(‘Calendar’[Month]))

VAR MyYear = [Year]

VAR MyStart = SUMX(FILTER(MaxMos,[Year]<MyYear),[MaxMonth])

VAR firstYear = CALCULATE(FIRSTNONBLANK(‘Calendar’[Year],1),ALL(‘Calendar’))

VAR myNum = IF(MyYear=firstYear,[Month],MyStart+[Month])

RETURN myNum

There are likely limitations with the simple approach, though none have been uncovered as of this writing. Power BI can slice and dice in a hundred different ways and seems to care very little whether the code is complex or simple math.

I like simple.

Get the latest file from Power BI

Daily or weekly reports constantly need data appended to a source folder in non-database situations. As the files pile up folder it can be a little difficult to differentiate between them in Power BI, when one really only needs the latest for a particular visualization or report page.

Another story on Medium, linked below, does a great job of detailing how to get only the latest file data. The solution points to creating a separate, identical query on the same pile of data and then, singling out the most recent report in the new query.

A few standard conventions are required to do this. Naming files to include a YYYYMMDD date prefix makes it a little easier to perform the sort needed but inserts an extra “hands on” step in the process. [Date Modified] or [Date Created] from the file attributes usually prove more useful.

While all of this is effective enough, it can make things a little clunky; especially true if seeking to have the queries always mirror one another. There are quite a few elegant solutions to do just that too, if one digs around enough.

There is a simpler solution. It still requires sorting the data in Power Query. It does not require another query.

In Power BI, a slicer leveraging whatever field used to do that sort pulls off the same stunt. Place a “Top N” filter on that slicer to show only the “Top 1” based on whatever seems most appropriate. In my use case, it is a date field and works perfect every time, with every “Scheduled Refresh,” and appears a little more flexible too.

There are a few limitations on editing the text that appears in the slicer. A few modifications may be needed to get the right look. However, it enables the report to host as many visualizations as needed, based on one data file, without an extra query and the need to maintain that query. I suspect it probably performs better too.

. . .

further reading

Connect to datasets in the Power BI service from Power BI Desktop

Microsoft Learn

Getting data from the latest file in a folder using Power Query

Medium

Payroll Hours Paid Report

The Payroll Hours Paid report provides visibility into payroll hours and labor costs across the organization. It is used to monitor paid hours, labor expenses, overtime utilization, PTO usage, salary hours, and staffing costs by location, employee group, and reporting period. The report supports workforce planning, financial management, budget monitoring, and labor-cost analysis.

All Hours

The All Hours page provides a comprehensive overview of paid hours and associated labor costs across the organization.  This page serves as the primary executive dashboard for payroll utilization and labor cost monitoring.

The page allows users to:

• Review total labor hours, salary hours, overtime hours, differential hours, and PTO hours.
• Analyze labor and payroll costs by location.
• Monitor paid-hour trends over the previous 13 calendar months.
• Compare labor utilization across organizational units.
• Track workforce-related expenses over time.

Labor Hours

The Labor Hours page focuses specifically on worked labor hours and staffing utilization.  This page serves as the operational labor analysis page for the report.

The page is intended to help users:

• Analyze labor-hour consumption.
• Monitor worked hours separate from salary and PTO hours.
• Evaluate workforce utilization patterns.
• Review labor-hour trends and staffing levels.

Decompose Labor Cost

The Decompose Labor Cost page is used to analyze and break down labor expenses into their contributing components.  This page serves as the report's labor-cost analysis and troubleshooting page.

The page helps users:

• Identify factors driving labor costs.
• Compare labor costs across locations and departments.
• Understand contributors to payroll expense fluctuations.
• Investigate high-cost operational areas.

Decompose Labor Hours

The Decompose Labor Hours page provides deeper analysis into the factors contributing to labor-hour utilization.  This page supports detailed workforce utilization analysis.

The page allows users to:

• Investigate labor-hour drivers.
• Analyze staffing utilization patterns.
• Compare labor-hour allocations across organizational units.
• Understand variations in worked hours.

OT Hours vs OT Cost

The OT Hours vs OT Cost page focuses specifically on overtime utilization and the associated payroll expense. This page serves as the report's primary overtime management and labor-cost monitoring page, helping leadership understand the relationship between overtime usage and payroll expense. It is intended to support workforce planning, scheduling decisions, and labor cost control efforts.

The page allows users to:

• Compare overtime hours against overtime labor costs.
• Identify locations generating the highest overtime expenses.
• Determine whether overtime cost increases are driven by more overtime hours, higher labor rates, or both.
• Monitor overtime trends over time.
• Evaluate the financial impact of overtime utilization across programs and departments.

Version Notes

The Version Notes page documents report calculations, business rules, data source changes, enhancement history, and report maintenance activities. It serves as the technical documentation for the report and provides reference information for users who need to understand how the report is built, maintained, and updated.