cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paulgalve-mrm
Helper I
Helper I

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:  

paulgalvemrm_0-1660182873515.png

 

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

paulgalvemrm_1-1660183355580.png

 

Thanks in advance!!!

PG

 

 

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
paulgalve-mrm
Helper I
Helper I

@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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur - apologies for the late reply.

 

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

Segment GroupFiscal YearFiscal QuarterFiscal DateValues
StrategicFY22Q115-Aug500
StrategicFY22Q131-Aug1000
StrategicFY23Q115-Aug500
(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

I am still struggling to understand your question.  I am sure, someone else will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
paulgalve-mrm
Helper I
Helper I

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)
paulgalve-mrm
Helper I
Helper I

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

paulgalve-mrm
Helper I
Helper I

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:

SegmentationFiscal YearFiscal QuarterActual
StrategicFY22FY22-Q1984
StrategicFY22FY22-Q21119
StrategicFY23FY23-Q160
EnterpriseFY22FY22-Q116605
EnterpriseFY22FY22-Q223451
EnterpriseFY23FY23-Q1162
CorporateFY22FY22-Q115342
CorporateFY22FY22-Q218957
CorporateFY23FY23-Q1558
SMC - ScaleFY22FY22-Q128749
SMC - ScaleFY22FY22-Q228128
SMC - ScaleFY23FY23-Q1258

 

Date Table:

DateFiscalMonthFiscalYearFiscalQuarterFiscalYearQtr
7/1/2021 0:0012022Q1FY22-Q1
7/2/2021 0:0012022Q1FY22-Q1
7/3/2021 0:0012022Q1FY22-Q1
7/4/2021 0:0012022Q1FY22-Q1
7/5/2021 0:0012022Q1FY22-Q1
7/6/2021 0:0012022Q1FY22-Q1
7/7/2021 0:0012022Q1FY22-Q1
7/8/2021 0:0012022Q1FY22-Q1
7/9/2021 0:0012022Q1FY22-Q1
7/10/2021 0:0012022Q1FY22-Q1
7/11/2021 0:0012022Q1FY22-Q1
7/12/2021 0:0012022Q1FY22-Q1
7/13/2021 0:0012022Q1FY22-Q1
7/14/2021 0:0012022Q1FY22-Q1
7/15/2021 0:0012022Q1FY22-Q1
7/16/2021 0:0012022Q1FY22-Q1
7/17/2021 0:0012022Q1FY22-Q1
7/18/2021 0:0012022Q1FY22-Q1
7/19/2021 0:0012022Q1FY22-Q1
7/20/2021 0:0012022Q1FY22-Q1
7/21/2021 0:0012022Q1FY22-Q1
7/22/2021 0:0012022Q1FY22-Q1
7/23/2021 0:0012022Q1FY22-Q1
7/24/2021 0:0012022Q1FY22-Q1
7/25/2021 0:0012022Q1FY22-Q1
7/26/2021 0:0012022Q1FY22-Q1
7/27/2021 0:0012022Q1FY22-Q1
7/28/2021 0:0012022Q1FY22-Q1
7/29/2021 0:0012022Q1FY22-Q1
7/30/2021 0:0012022Q1FY22-Q1
7/31/2021 0:0012022Q1FY22-Q1

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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

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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.