YoY Comparison without date

[FabioB7]

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

[v-pgoloju]

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

[v-pgoloju]

Hi @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

[v-pgoloju]

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 Kumar

[FBergamaschi]

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]

    )

RETURN

Full_Calendar

Then connect this table to the fact table (Date[Date] connected to the relevant date in your fact table)

Finally wirte this measure

Sales = 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 report

Best

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

[FabioB7]

@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

[FBergamaschi · Accepted solution]

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

[FBergamaschi]

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

[FabioB7]

@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?