Create Dynamic Periods for Fiscal or Calendar Dates and Dynamic Month Names for Charts 09-06-2016 22:27 PM - last edited 03-16-2017 17:02 PM

[GilbertQ]

For this data story, my first goal was to be able to create any Date Period that was required for the data. As with the example we have creaated a few simple Periods "CY-LY" (Calendar Year - Last Year), "CY-YTD" (Calendar Year - Year to Date). And in doing so, it gives the user the ability quickly slice their data by the defined period.

Second to this I also created a solution in which the Month Names would dynamically change to either the Fiscal Months (If a Fiscal Period was selected) or Calendar Months (If a Caleandar Period was selected).

So in the report if you click on "CY-YTD", you will see that the dates follow "Jan-May"

Whilst if you click on "FY-LY", you will see that the dates follow the Fiscal Periods "Nov - October"

The final part of the data story is that using the Periods, it makes it very easy to create Calculated Measures based on your custom periods. And his is a lot easier and quicker when your custom periods do not have built in DAX functions.

In doing so that this does is it enables the user to have the ability to quickly and easily slice their data based on custom periods.

I have also published a blog post in terms of how I achieved this.

Create Dynamic Periods for Fiscal or Calendar Dates in Power BI

eyJrIjoiZGNjNGUzM2ItZWVhOS00ZWRlLWFlZmEtZjljMDZlMmNmMTY0IiwidCI6ImQ3MGI0ZDU1LWE1OTItNDVmMi05OWYzLTI5OWFlNWVlN2FkZSJ9

[amirsheva]

Hello, please could you send  me the PBIX File.

khakdoustamir@gmail.com

  Many thanks.

[GilbertQ]

Hi @amirsheva 

If you click on the blog post, and then go to the end you will see the option to download the PBIX

[estapeace]

Do you offer Power BI classes?

[GilbertQ]

Hi there, I currently do not, but it depends what your requirements are and the number of people willing to attend.

[Anonymous]

Very good dashboard and financial calender is what a lot of companies use.

What did you use to publish this dashboard into a webpage.

I am new to this environment.

regards

willy

[GilbertQ]

Hi there.

I am glad you found it useful. I do also have a blog post and pbix file explaining how I achieved this.

The feature is called publish to web after you upload your Power BI desktop file to the Power BI service. You can then click on file and then Publish to Web.

Just note that when you publish to web it is available for anyone who has access to the link.

[ayami123]

let

TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),

YearText = Number.ToText(2000),

FiscalStartMonth = Date.Month(Date.FromText(“”&YearText&”-“&#”Fiscal Start Month”&“”)),

FiscalStartMonthDay = Date.Day(Date.StartOfMonth(Date.FromText(“”&YearText&”-“&#”Fiscal Start Month”&“”))),

FiscalEndMonth = Date.Month(Date.FromText(“”&YearText&”-“&#”Fiscal End Month”&“”)),

FiscalEndMonthDay = Date.Day(Date.EndOfMonth(Date.FromText(“”&YearText&”-“&#”Fiscal End Month”&“”))),

where did he edit this, =_= I don't know where I couldn't replicate the example =_=

[GilbertQ]

Hi there, I edited this in the query editor.

[ayami123]

I know that you use the query editor, but you can't do that unless you click on some table, if I created some blank query, I also need to put output and it gives me error when i do that since i'm missing the output

[GilbertQ]

Ok thanks do you perhaps have the error, that I can have a look at?

[HalilG]

Very nice report. Thanks for the blog post too. May I ask what's the KPI like visual u use on page 3? Where can I find it?

[GilbertQ]

Hi there

Thanks for the kind words. And the visual on page 3 was done by a 3rd party developer.

You can contact him on contact@dax365.com his name is Nick Savenko

[HalilG]

thank you for the information.

[DonAldridge]

Great blog post Gilbert. Very detailed. Thanks for sharing with the community.

[daandamhuis]

Nice Dashboard, really like the feature which allows to select the unit of measure and the different date periods. By the way, on Page 4 there is a breakdown tree. Is it possible to share the visualization, I was searched for this since Performance Point.