Forum Discussion
Multiple Calendars
Is it possible (how do you) to have multiple calendars? I would like a main calendar with a normal month-end cut off, such as February, 2022. I would also like a calendar which shows monthly budget target, which would have a cut off of December, 2022. Thank you for any help.
Hi ShrimpSurf
No worries mate. You can use simple calendar / date tables using DAX:
- https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
- https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-bi
And Power Query version: https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query
Whichever way you go, I recommend that you use the same method for both. All you need to do with the above is just adjust the Start / End Dates to what you need.
If you're after a simple version that you can modify easily, refer: https://powerbi.tips/2017/11/creating-a-dax-calendar/
Dates = GENERATE ( CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ), VAR currentDay = [Date] VAR day = DAY( currentDay ) VAR month = MONTH ( currentDay ) VAR year = YEAR ( currentDay ) RETURN ROW ( "day", day, "month", month, "year", year ) )Hope this helps mate.
Theo
3 Replies
Hi ShrimpSurf
I recommend having your main calendar / date table and, separately, just have a CSV / XLSX file for your End of Reporting period. Basically, just add a date in cell A1 (being the End of Reporting period) and then you can use that as the basis rather than maintaining two completely separate Calendar / Date tables.
Hope I have understood your question correctly and apologise if I haven't!
Theo 🙂
Thanks Theo, however do not wish to provide the analysis outside of PowerBI. I'm looking for a solution within PBI>
Hi ShrimpSurf
No worries mate. You can use simple calendar / date tables using DAX:
- https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
- https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-bi
And Power Query version: https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query
Whichever way you go, I recommend that you use the same method for both. All you need to do with the above is just adjust the Start / End Dates to what you need.
If you're after a simple version that you can modify easily, refer: https://powerbi.tips/2017/11/creating-a-dax-calendar/
Dates = GENERATE ( CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ), VAR currentDay = [Date] VAR day = DAY( currentDay ) VAR month = MONTH ( currentDay ) VAR year = YEAR ( currentDay ) RETURN ROW ( "day", day, "month", month, "year", year ) )Hope this helps mate.
Theo
