Power BI – Creating Dashboard Items that show MTD (Month to Date), or WTD (Week to Date), or YTD (Year to Date) dynamically changing based on your current period
I have been working quite a lot with Power BI, and one of the great features is Q&A
So what if you wanted to create a dashboard item that would always show you your sales for the current month (MTD), or current week (WTD) or current Year (YTD)? And always show the correct current period, without you having to go and change it manually?
But you do not want to have to try and do this in your reporting layer, as this would affect all your reports, but want it to be used as it should as a dashboard?
What it looks like once finished.
As you can see below in my Q&A this is very easy to do. And the image below shows what it looks like once I have made a few changes to the initial Q&A question.
As you can see Power BI is context aware for dates, and it knows that we are in the month of August, and it is displaying our data from 01 Aug 2016 – 23 Aug 2016 (Todays date)
How to do it
In order to get the same visual as shown above I did the following:
First I typed it into Q&A and I got the following screen:
Next what I did was to click on the Visualizations, Filters and Fields on the right hand side to expand them.
I then clicked on the Visualizations and selected the Area Chart as shown below in the Visualizations section
What this did was then show me the following in the Canvas
The final step was for me to go into the Fields list and select a Date value from my Date table. And for my example I just chose the actual date. But you can choose any option you want to visualize.
NOTE: When you add in the Date, by default it will create the hierarchy in your Visualizations Axis as shown below.
I then clicked on the down arrow and selected Date
Once that is done you then get the same visual as first shown.
Likewise, if I change this to look at YTD (Year to Date) I change it to “This Year” and complete the steps above.
NOTE: Another thing to remember is that you can modify any of the Visualization
settings as you would with any Visual. As you can see above I changed the colour to Grey.
And finally I also created a dashboard item for Last Year
Tip – How to get the Dashboard Item from Q&A to link to your report
A quick tip is that if you still want your dashboard item you created above to go to your underlying report and NOT to your Q&A question once you have pinned your dashboard item complete the following.
First go into your report and click on the sheet that you want it to be linked to.
Now if you look in the URL you will see the URL as well as the ReportSection as shown below.
Now take that entire link, go into your dashboard item, click on the ellipses and then click on Tile Details
Then on in the tile details click on the Set Custom Link
And the paste in your URL from the steps above as well as ensure the option to “Open custom link in the same tab? Is set to No
Now when you click on the dashboard item it will take you to the correct sheet on your report.
A final note is that it does appear that you can use “This year“, “Last Year“, “Last Two Years” etc. on any date field and it will apply.