Multiple Calendars

[ShrimpSurf]

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.

[TheoC]

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 🙂

[ShrimpSurf]

Thanks Theo, however do not wish to provide the analysis outside of PowerBI.  I'm looking for a solution within PBI>

[TheoC · Accepted solution]

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-...

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