Sorting months in fiscal calendars in DAX
Vložit
- čas přidán 10. 08. 2020
- This video shows a basic technique used to sort months according to a fiscal calendar, by using a couple of calculated columns and the “sort by column” feature of Power BI. Article and download: sql.bi/492713?aff=yt
- Věda a technologie
thank you for this tutorial. been trying to figure this out and you tutorial is so easy to follow for newbie like me in power bi
Alberto, tante grazie por tus clases. Estoy aprendiendo muchísimo con tus clases (y las de Marco también, por supuesto). You both are very generous for giving us such fantastic content for free (I think of your online courses at SQLBI's web too, not only these videos). Thank you very much! Saludos desde Buenos Aires, Argentina!
As if our lives were not difficult enough but .... Fiscal Month. Thanks for this!
Just what i was looking for. Hats off Alberto great explanation.
Sometimes we need a refresher. Great video @alberto!!!!
Great video, as always! Thank you!
Bravo!
Thanks for your Great Job. You are GURU more me!!!
Alberto, thanks for sharing valuable information
Thanks for great video :)
GREAT WORK.HELPFUL
I'm back again, looking over this info. I'm creating a independent date hierarchy (FY / FY Month Year / Date) and now I need to sort the FY Mth Year. I guess I have to create Month plus Year value though it's not so straight forward...Happy holidays to you!
(And I did find a solution - it was much easier in PQ - I created a cust col with Date.StartofMonth and then converted that to a whole number, and used that to sort my Mth/Years)
Hi Alberto, have you ever covered the topic of dynamic toggling by the user between Calendar and Fiscal years? I mean displaying the sales by months, either calendar or fiscal months, according to user choice. I tried to do it with Field Parameters however it always collapses Months in the matrix to full Years. And the Field Parameters do not seem to allow changing sort of Months as you demonstrated in the video. The only way left is bookmarks, however this is a "dirty" approach.
In my case fiscal month starts from December, what formula should I apply?
Great technique, thank you :)
Hi Alberto, Thanks for the video, it really helped me a lot, but one question > How to get month name as "mmm" in the Fiscal month?
See these articles/tools:
www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/
www.daxpatterns.com/time-patterns/
www.sqlbi.com/tools/dax-date-template/
Hello, How can we get a YTD out of that? I can't add my Variable "Fiscal Months" to my formula as it is not considered as a date=> YTD = CALCULATE (SUM('YTD Test'[Actual Sales Turnover]),DATESYTD('YTD Test'[Date for YTD].[Date]))
thanks
DATESYTD has an argument for the end of year, but you can also evaluate one of the many patterns available here: www.daxpatterns.com/time-patterns/
What about calendar with hours?
You should split date and time in two different columns. You can create a Time table similarly to what you do with a Calendar table, choosing the granularity you prefer (hours, minute, second). Keep in mind that reducing granularity improve performance: time by hour is faster than time by second.
This is incorrect and will fail if you have any month greater than June(6). Try to run this DAX for any starting month JULY(7) to DECEMBER(12) and check on the "Fiscal Month Number".
The proper solution when suppose the start month is OCTOBER(10):
Fiscal Month Number = IF('Date'[Month Number] > 9, 'Date'[Month Number] - 9, 'Date'[Month Number] + 3)
Yes the code is just a sample and has to be adapted to the beginning of the fiscal year. You can find a more complete and flexible implementation here: www.sqlbi.com/tools/dax-date-template/