Need to create YOY and QOQ

putturps · May 28, 2020, 1:42pm

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

BrianJ · May 28, 2020, 1:55pm

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

https://forum.enterprisedna.co/t/calculate-difference-this-year-vs-last-year-in-power-bi-with-dax/506

Brian

putturps · May 28, 2020, 1:58pm

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

Melissa · May 28, 2020, 2:10pm

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

putturps · May 28, 2020, 2:26pm

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.

BrianJ · May 28, 2020, 2:43pm

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

https://info.enterprisedna.co/dax-function-guide/calendarauto/

https://info.enterprisedna.co/dax-function-guide/addcolumns/

i hope this is helpful.

Brian

BrianJ · May 28, 2020, 2:53pm

@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

BrianJ · May 28, 2020, 4:44pm

@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):

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

as mentioned above, you will have to re-create the virtual date table in every measure
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
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
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…

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

Heather · May 28, 2020, 7:06pm

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

putturps · June 3, 2020, 2:53am

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

Definately will check on the petition

putturps · June 3, 2020, 2:53am

putturps · June 3, 2020, 2:55am

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
)
)

putturps · June 3, 2020, 2:55am

Thank you all for wonderful support.