CALENDARAUTO

[AlineTCarvalho]

Hello

I am trying to create a dCALENDAR with CALENDARAUTO, and my fiscal year starts in April, therefore April should be Q1 for instance.

However when I use the QUARTER function, power bi understands calendar year (jan - dec).
Please can someone help me and see what I am doing wrong?

I appreciate your help 😃

DAX:

Calendarauto Data =

VAR MinYear = YEAR(MIN(fLedger[Posting Date]))

VAR MaxYear = YEAR(MAX(fLedger[Posting Date]))

 RETURN

ADDCOLUMNS(

    FILTER(

        CALENDARAUTO(3),

        YEAR ([Date]) >= MinYear &&

        YEAR ([Date]) <= MaxYear

    ),

    "Year", YEAR([Date]),

    "Quarter", "Q" & QUARTER([Date]))

[Greg_Deckler · Accepted solution]

@AlineTCarvalho You can potentially use this fiscal calendar: DAX Custom 445 Calendar - Microsoft Fabric Community

Otherwise, Melissa de Korte's Power Query function for creating calendar tables is probably a good bet. Extended Date Table Power Query M Function | Master Data Skills + AI (enterprisedna.co)

[lbendlin · Accepted solution]

"Quarter", "Q" & QUARTER(EDATE([Date],3)))

[v-tianyich-msft · Accepted solution]

Hi @AlineTCarvalho ,

Thank @lbendlin  very much for his solution! But there is a small error, it should be "Quarter", "Q" & QUARTER(EDATE([Date],-3))). On that basis, I can give you a more complete date table, which can be seen in the following results:

DimDate = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "DayName", FORMAT ( [Date], "DDDD" ),
    "WeekDay", WEEKDAY ( [Date],2 ),
    "WeekStart",
        [Date] - WEEKDAY ( [Date], 2 ) + 1,
    "Quarter", "Q" & QUARTER(EDATE([Date],-3))
)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[AlineTCarvalho]

Thank you! I figured that because I wasn't getting the result right. 

I really aprreciate your input! 

[lbendlin · Accepted solution]

"Quarter", "Q" & QUARTER(EDATE([Date],3)))

[AlineTCarvalho]

Hi @lb_rlb brilliant, easy way to fix it.

Thank you so much for your help!

this is how it turned out:

Calendarauto Data =

VAR MinYear = YEAR(MIN(fLedger[Posting Date]))

VAR MaxYear = YEAR(MAX(fLedger[Posting Date]))

 RETURN

ADDCOLUMNS(

    FILTER(

        CALENDARAUTO(3),

        YEAR ([Date]) >= MinYear &&

        YEAR ([Date]) <= MaxYear

    ),

    "Year", YEAR([Date]),

    "Quarter", QUARTER(EDATE([Date],-3)))

[Greg_Deckler · Accepted solution]

@AlineTCarvalho You can potentially use this fiscal calendar: DAX Custom 445 Calendar - Microsoft Fabric Community

Otherwise, Melissa de Korte's Power Query function for creating calendar tables is probably a good bet. Extended Date Table Power Query M Function | Master Data Skills + AI (enterprisedna.co)

[AlineTCarvalho]

Hey, Greg!
Thank you so much. Your solution has helped me to figure this out.

This how it turned out:

Calendarauto segundo teste =

VAR MinYear = YEAR(MIN(fLedger[Posting Date]))

VAR MaxYear = YEAR(MAX(fLedger[Posting Date]))

 RETURN

 ADDCOLUMNS(

 ADDCOLUMNS(

 ADDCOLUMNS(

 ADDCOLUMNS(

ADDCOLUMNS(

    FILTER(

        CALENDARAUTO(),

        YEAR ([Date]) >= MinYear &&

        YEAR ([Date]) <= MaxYear

    ),

    "Year", YEAR([Date]),

    "Quarter CY", QUARTER([Date])),

    "QUARTER FY",

    SWITCH(TRUE(),

    QUARTER([Date]) = 1,"Q4",

    QUARTER([Date]) = 2,"Q1",

    QUARTER([Date]) = 3,"Q2",

    QUARTER([Date]) = 4,"Q3")),

    "FISCAL YEAR",

    SWITCH(TRUE(),

    [Date] >= DATE(2020,04,1) && [Date] <= DATE(2021,03,30),"FY2021",

    [Date] >= DATE(2021,04,1) && [Date] <= DATE(2022,03,30),"FY2122",

    [Date] >= DATE(2022,04,1) && [Date] <= DATE(2023,03,30),"FY2223",

    [Date] >= DATE(2023,04,1) && [Date] <= DATE(2024,03,30),"FY2324",

    [Date] >= DATE(2024,04,1) && [Date] <= DATE(2025,03,30),"FY2425")),

    "FY'Q",

    LEFT([FISCAL YEAR],4) &"'"& [QUARTER FY]),

    "Month Year",

    FORMAT([Date],"mmm")&"/"&

    FORMAT([Date],"yy"))