Need to create YOY and QOQ

Hi Team,

Need help, below are the two tables and need help on creating QoQ and YOY.

I know how to create using the inbuilt functions using a date table, however in this case I don’t have date table how to create it? Please guide me.

FiscalYearQuarter FiscalYear FiscalShortYear FiscalQuarter FiscalShortYearQuarter QuarterCount YearCount
FY2019-Q1 FY2019 FY19 Q1 FY19-Q1 1 1
FY2019-Q2 FY2019 FY19 Q2 FY19-Q2 2 1
FY2019-Q3 FY2019 FY19 Q3 FY19-Q3 3 1
FY2019-Q4 FY2019 FY19 Q4 FY19-Q4 4 1
FY2020-Q1 FY2020 FY20 Q1 FY20-Q1 5 2
FY2020-Q2 FY2020 FY20 Q2 FY20-Q2 6 2
FY2020-Q3 FY2020 FY20 Q3 FY20-Q3 7 2
FY2020-Q4 FY2020 FY20 Q4 FY20-Q4 8 2
FiscalYearQuarter REV COS
FY2019-Q1 13363.6339 11281.0086
FY2019-Q1 341.4293 225.7455
FY2019-Q1 1213.0032 661.9824
FY2019-Q1 1075.653 852.4935
FY2019-Q2 17771.7869 12749.2532
FY2019-Q2 203.6189 440.3383
FY2019-Q2 766.4631 1688.3353
FY2019-Q2 1043.426 888.0385
FY2019-Q2 0 -202.2466
FY2019-Q2 0 28.5594
FY2019-Q2 -2412.84 1276.3387
FY2019-Q3 7928.8113 8454.6616
FY2019-Q3 235.15 63.1129
FY2019-Q3 987.9789 925.9992
FY2019-Q3 1162.0524 954.9782
FY2019-Q3 0 0.0875
FY2019-Q3 0 11.0922
FY2019-Q3 395.0413 1553.2309
FY2019-Q3 4670.0431 4737.0333
FY2019-Q4 15512.8413 9986.8304
FY2019-Q4 3.8982 399.7855
FY2019-Q4 625.7281 792.9317
FY2019-Q4 1452.3517 1249.117
FY2019-Q4 0 0.3991
FY2019-Q4 -0.7553 19.3551
FY2019-Q4 -21.9767 965.0028
FY2019-Q4 5272.215 6903.4516

@putturps,

Please check out the following video. @Melissa walks through all the steps needed to create her awesome new extended date table. The capabilities in that new date table will definitely help you with this question and many other time intelligence problems.

Once you have your date table in place, this video should help you with the YoY and QoQ calculations.

  • Brian

Thanks Brain, but they need a separate date table to create. Asking us to use the existing columns to create.

Hi @putturps,

For any kind of time intelligence it’s required to have a proper Date table. By proper I mean that it’s a contiguous range of dates from the first date up to the last date in your model AND that every single day has just one row.

So if the first table you’ve depicted is your date dimension table then you’re in trouble…

Once you have a date table in place, the YoY and QoQ calculations will become straight forward with the links provided by @BrianJ

Thanks for the tip video and it really helpful. Is there any other way to fix this? I can’t go for date table creation here that’s the limitation what I have.

Looking for alternative solution by fixing by dax.

Appreciate your time and support.

@putturps,

Wow, that’s a strange restriction that is really gonna put you at a disadvantage and make what otherwise would be a simple calculation quite difficult.

I think if I had to do it this way, I would try the following:

Within each time intelligence measure, use the CALENDARAUTO() function combined with ADDCOLUMNS to create a virtual date table within a variable. Then build the time intelligence calculations off of that virtual table.

i hope this is helpful.

  • Brian
1 Like

@putturps,

I just found the article below, which illustrates exactly what I was talking about above. However, instead of creating this as a physical table per the article, you’ll use the same code to create the table virtually within a variable. If there’s any silver lining in this, it’s that once you get the code for the variable exactly the way you want it, you can reuse it in each of your time intelligence measures.

  • Brian

@putturps,

OK, your question really spurred my curiosity as to whether the virtual date table approach I proposed above would actually work, so I tested it out.

The good news: it does work (see screenshot below):

image

The bad news: as expected, it’s going to be a royal pain to implement for four reasons:

  1. as mentioned above, you will have to re-create the virtual date table in every measure
  2. canned time intelligence functions such as DATEADD, DATESBETWEEN, etc. that would’ve been really helpful are not going to work, because they can’t call a virtual date column created in a variable from ADDCOLUMNS or CALENDARAUTO. Thus, you are basically going to have to rebuild these functions using CALCULATE/FILTER combos
  3. you are going to have to use TREATAS in every time intelligence measure you create in order to create a virtual relationship between your virtual date table and your fact table
  4. you are going to have to filter on your fact table date, rather than your date table date (workable, but not ideal) because the only way to get TREATAS to properly build the virtual relationship is to create a separate one column CALENDARAUTO table with dates only (this is because the VALUES or DISTINCT functions you would typically used to create the first argument in the TREATAS function won’t accept virtual table columns, only physical columns).

Here’s the sample measure for Total Sales LY using the embedded virtual date table approach:

Total Sales LY Virtual Date Table = 

VAR vOneColumnTableDates =
CALENDARAUTO( 7 )

VAR vDateTable =
ADDCOLUMNS(
    vOneColumnTableDates,
    "@Year", YEAR( [Date] ),
    "@MonthNum", MONTH( [Date] )
)

VAR TotSalesLY =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        YEAR( Sales[Sales Date] ) = YEAR( TODAY() ) - 1
    ),
    TREATAS(
        vOneColumnTableDates,
        Sales[Sales Date]
    )
)

RETURN
TotSalesLY

I hope this is helpful. Full solution file attached.

  • Brian

P.S. Alternatively, we could start a petition to your employer to allow you to use a real date table… :grinning:

eDNA Forum –Virtual Date Table Solution.pbix (521.8 KB)

2 Likes

@putturps - I’ll sign the petition @BrianJ has proposed :laughing:

1 Like

Thank you Brain for sharing different options of calculation. Its going to be good learning for me.

Definately will check on the petition :slight_smile:

:smiley: :smiley: :smile:

Below is what I have tried and it worked as expected.

Previous Qtr mNetRev =
CALCULATE (
[mNetRevenue],
FILTER (
ALL ( FiscalQuarter ),
FiscalQuarter[QuarterCount]
= MAX ( FiscalQuarter[QuarterCount] ) - 1
)
)

Thank you all for wonderful support.

1 Like