Forum Discussion

AlineTCarvalho's avatar
AlineTCarvalho
New Member
2 years ago
Solved

CALENDARAUTO

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 unders...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    AlineTCarvalho
    New Member
    2 years ago

    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"))