Forum Discussion

FabioB7's avatar
FabioB7
New Member
1 year ago
Solved

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...
Need Help
  • FBergamaschi's avatar
    FBergamaschi
    1 year ago

    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's avatar
FBergamaschi
Super User
1 year ago

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's avatar
    FabioB7
    New Member
    1 year ago

    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's avatar
      FBergamaschi
      Super User
      1 year ago

      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