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



 




Need Help
  • 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.

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

    • AlineTCarvalho's avatar
      AlineTCarvalho
      New Member
      2 years ago

      Hi Anonymous 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)))
    • 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"))



  • Anonymous's avatar
    Anonymous
    Not applicable
    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

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

      I really aprreciate your input!