The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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]))
Solved! Go to 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)
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.
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.
Thank you! I figured that because I wasn't getting the result right.
I really aprreciate your input!
"Quarter", "Q" & QUARTER(EDATE([Date],3)))
Hi @lb_rlb brilliant, easy way to fix it.
Thank you so much for your help!
this is how it turned out:
@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)
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"))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
135 | |
101 | |
89 | |
72 | |
58 |
User | Count |
---|---|
262 | |
120 | |
115 | |
95 | |
82 |