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