Forum Discussion

vjnvinod's avatar
vjnvinod
Icon for Impactful Individual rankImpactful Individual
4 years ago

Need help with Calendar building

Dear experts

 

 

below is my financial calendar, I need to create a calendar on the Power BI desktop.

how do I do this? so that all of the below screenshot logics come up?

 

 

 

Need Help
Show and Tell

7 Replies

    • vjnvinod's avatar
      vjnvinod
      Icon for Impactful Individual rankImpactful Individual
      4 years ago

      Greg_Deckler 

      this is awesome, one formula and you have everything

      but unfortunately, I am not a DAX expert and editing this formula to match my requirement is possibly not my cup of tea

      is there any way you can tweak this and help me with this calendar based on what I need in the screenshot attached calendar, if its not too much of work for you

      i would really appreciate your help

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        4 years ago

        I believe you want this:

         

        Custom544 = 
    VAR __StartYear = 2021 // starting year
    VAR __NumYears = 2 // number of years including start year
    VAR __WeekForm = 16 // 16 has Saturday as 1, Friday 7
    VAR __StartDay = 1 // weekday to start calendar on
    VAR __StartWeek = 27 // # instance of weekday to start calendar on (5th Saturday for example)
    VAR __Base = CALENDAR(DATE(__StartYear,1,1),DATE(__StartYear,12,31))
    VAR __StartDate = 
        MAXX(
            FILTER(
                ADDCOLUMNS(
                    __Base,
                    "WeekNum",COUNTROWS(FILTER(__Base,[Date]<=EARLIER([Date]) && WEEKDAY([Date],16) = __StartDay))
                ),
                [WeekNum]=__StartWeek && WEEKDAY([Date],16)=__StartDay
            ),
            [Date]
        )
    VAR __CalendarBase = CALENDAR(__StartDate,__StartDate + 52 * __NumYears * 7 - 1)
    VAR __Calendar = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    ADDCOLUMNS(
                        __CalendarBase,
                        "Year",ROUNDUP(([Date]-__StartDate+1)*1./ (52*7),0)-1+__StartYear,
                        "DAYOFWK#",MOD(([Date] - __StartDate),7)+1,
                        "SEQWK#",COUNTROWS(FILTER(__CalendarBase,[Date]<=EARLIER([Date]) && WEEKDAY([Date],16) = __StartDay)),
                        "DAY#YEAR",MOD(([Date]-__StartDate)*1.,(52*7))+1,
                        "DAY",DAY([Date])
                    ),
                    "WK#",ROUNDUP([DAY#YEAR]/7,0),
                    "QWK#",MOD([SEQWK#]-1,13)+1,
                    "Q",ROUNDUP([DAY#YEAR]/91,0)
                ),
                "Month",SWITCH(TRUE(),
                            [Q]=1 && [QWK#]<=5,1,
                            [Q]=1 && [QWK#]<=9,2,
                            [Q]=1,3,
                            [Q]=2 && [QWK#]<=5,4,
                            [Q]=2 && [QWK#]<=9,5,
                            [Q]=2,6,
                            [Q]=3 && [QWK#]<=5,7,
                            [Q]=3 && [QWK#]<=9,8,
                            [Q]=3,9,
                            [Q]=4 && [QWK#]<=5,10,
                            [Q]=4 && [QWK#]<=9,11,
                            [Q]=4,12
                        )   
            ),
            "MonthName",
                SWITCH([Month],
                    1,"P1",
                    2,"P2",
                    3,"P3",
                    4,"P4",
                    5,"P5",
                    6,"P6",
                    7,"P7",
                    8,"P8",
                    9,"P9",
                    10,"P10",
                    11,"P11",
                    12,"P12"
                )
        )
RETURN
    __Calendar

         

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi vjnvinod 

     

    You want to build out your date table like in the attachment.  You have a series of columns that relate to calendar dates.  Another group of columns that are "reporting" or fiscal calendar.  Hope this helps!

    • vjnvinod's avatar
      vjnvinod
      Icon for Impactful Individual rankImpactful Individual
      4 years ago

      littlemojopuppy 

       

      I think I am looking at building this in power BI desktop using Calendar functions and logics

      I have used the calendar function but not been successful in other logic like week start date, week end date, Month, etc (like what you see in my screenshot)