Han Solo’s Smugglers Guide to Power BI: Dynamic Data Analysis using Dates and “Loops”
In my last post, #SmugglersGuidetoPowerBI, I shared some Power BI DAX Measures to utilize dates selected in a multi-date slicer. I looked at the DAX statements needed to determine: the most recent date selected; the date before the most recent date; and how to find a date 12 months before the most recent date. Now that we have seen one way to determine the end date selected in the multi-date slicer, I want to examine how to determine the start (beginning) date selected in the multi-date slicer. Once we have these two dates, we can begin to perform useful analysis and forecasting.
?
I also want to touch on how to perform “pseudo-for loops” and/or “pseudo-while loops” in Power BI. As you know, true loop constructs cannot be performed directly in Power BI. However, Smugglers can use knowledge of little-known hyperspace lanes to evade capture and to perform these forbidden loops. For now, I what to submit that the output from knowing the start and end dates can be used for input of these loops. I will get you started, and from there the star system destinations you choose will be your own.
?
As noted from the previous post, the end (most recent) date selected in the multi-date slicer can be determined using the following.
?
Selected End Date =
VAR _ChangeEndDate =
? ? ALLSELECTED ('Change'[Date])
VAR _SelectedEndDate =
? ? CALCULATE (
? ? ? ? MAX('Change'[Date]),_ChangeEndDate)
RETURN
? ? _SelectedEndDate
?
A minor change is all that is needed to determine the earliest date selected in the multi-date slicer. As you can see in the following, I replaced MAX with MIN.
?
Selected Start Date =
VAR _ChangeStartDate =
? ? ALLSELECTED ('Change'[Date])
VAR _SelectedStartDate =
? ? CALCULATE (
? ? ? ? MIN('Change'[Date]),_ChangeStartDate)
RETURN
领英推荐
? ? _SelectedStartDate
?
Now we can use these same statements in a Measure to calculate the time between the two selected dates. In my case I am working with Months as my time periods of interest.
?
Months Between Selected =
VAR _ChangeStartDate =
? ? ALLSELECTED ('Change'[Date])
VAR _SelectedStartDate =
? ? CALCULATE (
? ? ? ? MIN('Change'[Date]),_ChangeStartDate)
VAR _ChangeEndDate =
? ? ALLSELECTED ('Change'[Date])
VAR _SelectedEndDate =
? ? CALCULATE (
? ? ? ? MAX('Change'[Date]),_ChangeEndDate)
RETURN
? ? DATEDIFF(_SelectedStartDate, _SelectedEndDate, MONTH)
?
Now that we have the number of months between the two dates, we can use that number as input for the loop statements: __n in the link that follows. The author here does a great job, using examples of how to use the DAX GENERATESERIES function as a “proxy” for the loop construct. I hope to see some of your designs and solutions using loops, and I will provide some of mine in a later post.
?
For and While Loops in DAX
?
It’s time for we smugglers to load some valuable cargo utilizing the date measures in combination with the loop constructs discussed in the link. Be sure and read the entire link, as you will see this concept coming into play in a real-world example in the section titled, “The Practical (Useful) Part”. I will follow-up on some concepts I have touched on in future posts. Also, in my next post, I would like to look at another way of using the GENERATESERIES function to provide user input variables; i.e., another hidden hyperspace lane in Power BI.