Bi-Monthly Reporting - how to set-up calendar?

[Anonymous]

Hi everyone!

I have a data that comes bi-monthly: (DJ is December-January; FM - February-March, etc.). I was wondering how can I setup a calendar that way so the data type would be seen for Power BI as date. Here's the table:

Thank you in advance!

Best,

Zhibek

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can extract original table date fields to create a new calendar table with custom column 'bi-month', then use it to link to the raw table date field. After these steps, you can mapping your records to the real calendar date to calculate and analysis

Calendar =
VAR yearlist =
    SELECTCOLUMNS ( 'Table', "Year", RIGHT ( [Date], 4 ) )
RETURN
    ADDCOLUMNS (
        CALENDAR (
            DATE ( MINX ( yearlist, [Year] ), 1, 1 ),
            DATE ( MAXX ( yearlist, [Year] ), 12, 31 )
        ),
        "Bi-Month",
        VAR curr = [Date]
        VAR _month =
            IF (
                MOD ( MONTH ( curr ), 2 ) = 0,
                LEFT ( FORMAT ( curr, "MMM" ), 1 )
                    & LEFT ( FORMAT ( DATE ( YEAR ( curr ), MONTH ( curr ) + 1, 1 ), "MMM" ), 1 ),
                LEFT ( FORMAT ( DATE ( YEAR ( curr ), MONTH ( curr ) - 1, 1 ), "MMM" ), 1 )
                    & LEFT ( FORMAT ( curr, "MMM" ), 1 )
            )
        VAR _year =
            IF ( MONTH ( curr ) < 11, YEAR ( curr ), YEAR ( curr ) + 1 )
        RETURN
            _month & " " & _year
    )

Regards,

Xiaoxin Sheng

[Greg_Deckler]

Probably going to be a SWITCH statement like:

Column = 

  SWITCH(TRUE(),

    LEFT([Date],1) = "D",DATE(RIGHT([Date],4),12,1),

   ...

  )

[Anonymous]

Dear Greg @Greg_Deckler ,

Thank you for such a quick response!

I am a beginner and didn't quite get that... could you please briefly explain what "D", 4),12,1) - mean in the formula? And should I add it as a custom column? 

For now my calendar looks like that: 

thanks!