Sort Month Text in multiple year

In Power BI, in some specific contexts I wish I could make a report using trend chart showing values over 12 months, from jan to dec and comparing the trend of more years at the same time.

However, it’s impossible sort data by two helper column (monthsort) as there are more distinct values in the sort column than the result column.

The solution is very easy: Continua a leggere

Power BI Diagnostics, Trace Logs And Query Execution Times (Again)

Chris Webb's BI Blog

I’ve blogged a few times now about how to monitor Power BI query execution times, most recently here, and while using functions like DateTime.FixedLocalNow() is all very well I’ve never been happy with the fact that you have to alter your queries in order to be able to time them. Using the Power BI trace logs always seemed a much better option – and since the log files are in a reasonably easy to understand text format, you can use a Power BI query to load data from them. I wrote a post on importing data from the trace logs (using Power Query) here back in 2014 and Rui Romano has a great post on doing the same thing for Power BI here. The big problem with the Power BI trace logs, though, is that there is too much information in them: they’re really meant for Microsoft internal…

View original post 980 altre parole

DAX Year to Date in Previous/Prior Year

How Are We Doing THIS Year Versus the Same Time LAST Year?

This is a pretty common question, and a pretty common need.  But there’s no DAX function that just DOES this.

For instance, getting a“Year to Date” calculation for, say, Total Sales, is pretty straightforward:

[YTD Sales] = CALCULATE([Total Sales], DATESYTD(Calendar[Date]))

Back to the point of this post:  it turns out that you can “nest” a DATESYTD inside of a DATEADD!

Try this:

[Prev Yr YTD Sales] = CALCULATE([Total Sales], DATEADD(DATESYTD(Calendar[Date]),-1,Year))

We have two requirement:

  • You must have a well-constructed Calendar/Dates table
  • You need to then USE that Calendar/Dates table on your pivot table

if you want more info click this link