One of those days where I spent a reasonable amount of time trying to solve a tricky DAX expression and once I got it; I came to know it was not at all tricky. In fact, it was far too simple to admit the time spent ?
When I showed PARALLELPERIOD and SAMEPERIODLASTYEAR measures to my business user, he asked me if he can select random quarters and measure should always get the variance from the earlier period based on the slicer choice than Time intelligence.
I got my haircut today (pretty spiffy one too, even if I do say so myself). While I was chatting I asked my hair dresser “on average, how often should I get my hair cut”? She told me (for men) around 4-6 weeks. Then I got thinking (as I do), I wonder if I could data-mine my credit card data using Power BI and find out how often I actually get my own hair cut. It turns out I was able to do this, and this article explains the hardest part of that task – find the number of days between two transaction dates using DAX.
The other day I discovered something new (at least to me) while writing the DAX for a measure in Power BI Desktop: when you insert a new line in your DAX expression using SHIFT-ENTER it also auto-indents the code. I asked a few people if this was new because I was sure I hadn’t seen it before, even though I always put line breaks in my code; of course Marco had and said he thought it had been around for a while
Last week I presented a webinar with Chuck Sterling (from Microsoft) on the topic of Time Intelligence. This was a YouTube broadcast event that anyone could join live, however it has also been recorded and can be watched at any time (see below).
Sometimes, just when you think you are getting the hang of the DAX language, something happens that completely baffles you. Such events can cause you to doubt your own learning and progress. But let me assure you – this happens to EVERYONE at sometime or another. In this article I am going to show you one such baffling situation, and then explain what is really happening.
Power BI provides great time intelligence features to calculate Year-to-date (YTD), Month-to-date (MTD) and Quarter-to-date (QTD) totals. There is no such thing as Week-to-date (WTD) or Period-to-date (PTD) where period could be any arbitrary period definition (I used two-month periods in my example below). If you want those, you will have to create the calculations yourself.
If you use Power BI to connect to a data source such as SQL Server in DirectQuery mode, you’ll find that by default you cannot use the complete range of DAX functions inside your calculations. For example, here’s a screenshot from Power BI Desktop in DirectQuery mode with a measure where I’m trying to use the RankX() function: