cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## QoQ, YoY Calculations not Working

Hi Experts!  I have a data model below.  Date and Fact tables are not related.  I need help in checking why the Same Qtr PY Actual column is not showing the right value.  I am using this measure to get the actual value from previous year same quarter.

Same Qtr PY Actual = CALCULATE( [Actual] , SAMEPERIODLASTYEAR( FYCalendar[Date] ) )

In this case, 61680 should be the expected output for FY23 Q1.
where:

Also, I would assume that if I extend the dataset with another grouping, this will apply the same calculation.

PG

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
13 REPLIES 13
Anonymous
Not applicable

@Ashish_Mathur - thanks for your help, I was able to apply it with my data model.  Just one question, what if I need to compare the previous year against the current year to date only?  Same goes with previous quarter against current quarter to date?

Say FY22-Q1 vs Fy23-Q1 and today is let say Aug 12.  Then, it should compare the FY22-Q1(Aug 12) to FY23-Q1(Aug 12) only.  I hope that explains what we are looking for.

Thanks!

Paul

Super User

I am not clear at all.  Share some data, show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Hi @Ashish_Mathur - apologies for the late reply.

Below shows of how we would like to calculated the Quarter to Date

 Segment Group Fiscal Year Fiscal Quarter Fiscal Date Values Strategic FY22 Q1 15-Aug 500 Strategic FY22 Q1 31-Aug 1000 Strategic FY23 Q1 15-Aug 500 (Expected Output is that is should only compare the values in red rows because the current fiscal quarter is only up to August 15th.

PROBLEM IS, we don't have the Fiscal Date column in the dataset.

Hope that helps.

Thanks!

PG

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Here's for the date table:

FYCalendar = ADDCOLUMNS(CALENDAR("7-1-2021","30-6-2024"),"DateAsInteger",FORMAT([date],"YYYYMMDD"),"Year",YEAR([Date]),"Monthnumber",FORMAT([Date],"MM"),"YearMonthNumber",FORMAT([Date],"YYYY/MM"),"YearMonthShort",FORMAT([Date],"YYYY/mmm"),"MonthNameShort",FORMAT([Date],"mmm"),"MonthNameLong",FORMAT([Date],"mmmm"),"DayOfWeekNumber",WEEKDAY([Date]),"DayOfWeek",FORMAT([Date],"dddd"),"DayOfWeekShort",FORMAT([Date],"ddd"),"Quarter","Q" & FORMAT([Date],"Q"),"YearQuarter",FORMAT([Date],"YYYY" & "/" & FORMAT ([Date],"Q")) )
FiscalMonth = (If( Month([Date]) >= 7  , Month([Date]) - 6,Month([Date]) + 6 ))

FiscalQuarter = "Q" & format([FiscalQuarterNumber],"0")

FiscalQuarterNumber = ROUNDUP ([FiscalMonth]/3,0)

FiscalYear = If( Month([Date]) >= 7  , Year([Date])+1,Year([Date]))

FiscalYearQtr = "FY"&Right(Format([FiscalYear],"0#"),2)&"-Q"&Right(Format([FiscalQuarterNumber],"0#"),1)
Anonymous
Not applicable

Apologies, can't attached the PBI file...

Anonymous
Not applicable

Sure @Ashish_Mathur .  Please see data attached below:

NOTE:  My Fiscal Year starts on July 1st and and June 30, thus show the date table below.

Fact Table:

 Segmentation Fiscal Year Fiscal Quarter Actual Strategic FY22 FY22-Q1 984 Strategic FY22 FY22-Q2 1119 Strategic FY23 FY23-Q1 60 Enterprise FY22 FY22-Q1 16605 Enterprise FY22 FY22-Q2 23451 Enterprise FY23 FY23-Q1 162 Corporate FY22 FY22-Q1 15342 Corporate FY22 FY22-Q2 18957 Corporate FY23 FY23-Q1 558 SMC - Scale FY22 FY22-Q1 28749 SMC - Scale FY22 FY22-Q2 28128 SMC - Scale FY23 FY23-Q1 258

Date Table:

 Date FiscalMonth FiscalYear FiscalQuarter FiscalYearQtr 7/1/2021 0:00 1 2022 Q1 FY22-Q1 7/2/2021 0:00 1 2022 Q1 FY22-Q1 7/3/2021 0:00 1 2022 Q1 FY22-Q1 7/4/2021 0:00 1 2022 Q1 FY22-Q1 7/5/2021 0:00 1 2022 Q1 FY22-Q1 7/6/2021 0:00 1 2022 Q1 FY22-Q1 7/7/2021 0:00 1 2022 Q1 FY22-Q1 7/8/2021 0:00 1 2022 Q1 FY22-Q1 7/9/2021 0:00 1 2022 Q1 FY22-Q1 7/10/2021 0:00 1 2022 Q1 FY22-Q1 7/11/2021 0:00 1 2022 Q1 FY22-Q1 7/12/2021 0:00 1 2022 Q1 FY22-Q1 7/13/2021 0:00 1 2022 Q1 FY22-Q1 7/14/2021 0:00 1 2022 Q1 FY22-Q1 7/15/2021 0:00 1 2022 Q1 FY22-Q1 7/16/2021 0:00 1 2022 Q1 FY22-Q1 7/17/2021 0:00 1 2022 Q1 FY22-Q1 7/18/2021 0:00 1 2022 Q1 FY22-Q1 7/19/2021 0:00 1 2022 Q1 FY22-Q1 7/20/2021 0:00 1 2022 Q1 FY22-Q1 7/21/2021 0:00 1 2022 Q1 FY22-Q1 7/22/2021 0:00 1 2022 Q1 FY22-Q1 7/23/2021 0:00 1 2022 Q1 FY22-Q1 7/24/2021 0:00 1 2022 Q1 FY22-Q1 7/25/2021 0:00 1 2022 Q1 FY22-Q1 7/26/2021 0:00 1 2022 Q1 FY22-Q1 7/27/2021 0:00 1 2022 Q1 FY22-Q1 7/28/2021 0:00 1 2022 Q1 FY22-Q1 7/29/2021 0:00 1 2022 Q1 FY22-Q1 7/30/2021 0:00 1 2022 Q1 FY22-Q1 7/31/2021 0:00 1 2022 Q1 FY22-Q1
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

@Ashish_Mathur - you just saved me a ton of time!  exactly what I'm looking for.  I'll apply those steps in my actual pbi file now.  Thanks a lot!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Why are the tables not related.  There should definitely be a relationship (Many to One and Single) between the Date column in your Data Table to the Date column in the Calendar Table.  In the Calendar Table, create caclualted columns for Year, Month name, Month number and Quarter.  To your visuals, drag the Date dimenstions from the calendar Table.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

@Ashish_Mathur - thanks for the reply.  I only have the Fiscal Quarter(FY22-Q1), and Fiscal Year(FY22) columns in the data table right now.  So, I can use the Fiscal Quarter column to relate the tables?

Super User

Hi,

Using whatever columns you have, we will first have to create a proper Date column and then build a Calendar Table from here.  Share the months which each quarter covers and also your PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com