Forum Discussion
YoY Comparison without date
Hi,
I am trying to create a YoY comparison based on fiscal quarter without having any calendar date, just pure fiscal quarter in text format
I have a bunch of quarters (Q324, Q424, Q125, Q225,....) and I want to compare for example Q225 vs. Q224.....I am not able to create anything on my side
Any helpful insight or any example to share?
Thanks a lot
We can solve this very easily, it is enough to create a calculate column in your fact table with the first date of each quarter, this column will be of type date (it will automatically get Date/Time Data type in Tabular, but you can convert it in Power BI Desktop (not on Power Query, it will not be visible there) to Date.
Calculated column name and code:
First Quarter Date =
VAR _Year = INT ("20"&RIGHT(YourFactQuarterColumn, 2))VAR _MonthNr =
VAR _QuarterNr = LEFT ( YourFactQuarterColumn, 2)
RETURN
IF (
QuarterNr = 1, 1,
IF (
QuarterNr = 2, 4,
IF (
QuarterNr = 3, 7,
10
)
RETURN
DATE ( _Year, _MonthNr, 1 )If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
8 Replies
You do not have a calendar table but are you allowed to create one in DAX ? I can provide the code.
Time intelligence is very easy with a calendar table but very hard without it
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
FBergamaschi , yes, I do not have a calendar table, that's my problem here, but I am allowed to create one in DAX (at least I think so) as I own the full database
Is this something that you can help me?
Yes I can
A simple calendar table is the following (creat a calculated table - Modeling --> New Table and paste the following code)
Date =VAR Full_Calendar =ADDCOLUMNS(ADDCOLUMNS(CALENDARAUTO(),"Date For Filters", [Date],"Year", YEAR ( [Date] ),"Month", MONTH ( [Date] ),"Month Name", FORMAT( [Date], "mmmm" ),"Qrt","Qrt " & QUARTER ( [Date] ),"WeekDay Nr", WEEKDAY( [Date], 2),"WeekDay Name", FORMAT( [Date],"dddd" ),"Week","Week " & WEEKNUM( [Date], 2 ),"YearMonth", YEAR([Date]) & FORMAT( MONTH( [Date] ),"00" )),"YearMonthNr", [Year] * 12 + [Month])RETURNFull_CalendarThen connect this table to the fact table (Date[Date] connected to the relevant date in your fact table)Finally wirte this measureSales = SUM ( your column with sales amount )Sales PY = CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )Sales YoY = [Sales] - [Sales PY]Sales YoY Pct = DIVIDE ( [Sales YoY], [Sales PY] )Now put in a visual the quarter in rows, the measures in values and enjoy your reportBestIf this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your threadconsider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
FBergamaschi , thanks for the help
Unlucky when I use your code, I get an error "CALENDARAUTO function can not find a base column of DateTime type in the model" and it seems to me reasonable as I do not have DateTime in my database (just fiscal quarter in text format), unless I am doing something wrong
I tried in different way to have my fiscal quarter (Q123, Q223, Q323,....) converted in a date, and somehow I was succesful but when I try to calculate the YoY using the followed DAX
Previous Month Revenue =CALCULATE([Total NR Calculated],DATEADD('Period Sort Table'[Quarter Calendar Date].[Month],-1,MONTH))I get also here an error "a column specified in the call to function 'dateadd' is not type of date. this is not supported", but I link to a column that's perfectly date format
I tried also with SAMEPERIODLASTYEAR, and getting the same error....like the date period reference I calculated is not correct while it's perfectly reflecting the period
We can solve this very easily, it is enough to create a calculate column in your fact table with the first date of each quarter, this column will be of type date (it will automatically get Date/Time Data type in Tabular, but you can convert it in Power BI Desktop (not on Power Query, it will not be visible there) to Date.
Calculated column name and code:
First Quarter Date =
VAR _Year = INT ("20"&RIGHT(YourFactQuarterColumn, 2))VAR _MonthNr =
VAR _QuarterNr = LEFT ( YourFactQuarterColumn, 2)
RETURN
IF (
QuarterNr = 1, 1,
IF (
QuarterNr = 2, 4,
IF (
QuarterNr = 3, 7,
10
)
RETURN
DATE ( _Year, _MonthNr, 1 )If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi FabioB7,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to FBergamaschi for prompt and helpful response.
Just following up to see if the response provided by community member were helpful in addressing the issue.
If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna KumarHi FabioB7,
Just following up to see if the response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
Best regards,
Prasanna Kumar
Hi FabioB7,
Just following up to see if the response provided by community member were helpful in addressing the issue.
If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.
Thanks & Regards,
Prasanna Kumar
