Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Quarter by Quarter Comparison (No Date Format, Date Format Only)

Hi everyone,

 

I recently created a power bi to illustrate forecasting performance but encountered a difficulty in quarter by quarter comparison. Following are two sample tables.

Sales Table

PeriodM1 SalesM2 SalesM3 Sales
FY20 Q1110120130
FY20 Q2210220230
FY20 Q3310320330

 

Forecast Table

PeriodM1 ForecastM2 ForecastM3 ForecastM4 ForecastM5 ForecastM6 Forecast
FY20 Q1101201301401501601
FY20 Q2102202302402502602
FY20 Q3103203303403503603

 

I would like to create two measures to compare sales volume with forecast volume in previous quarter and the one before.

Last Quarter Comparison in FY20 Q3 = M1 Sales in FY20 Q3 - M1 Forecast in FY20 Q2

Second Last Quarter Comparison in FY20 Q3 = M1 Sales  in FY20 Q3 - M4 Forecast in FY20 Q1

 

My expected final outcome is as follows.

PeriodM1M2M3
Last Quarter ComparisonSecond Last CompariosnLast Quarter ComparisonSecond Last CompariosnLast Quarter ComparisonSecond Last Compariosn
FY20 Q2210-101 220-201 230-301 
FY20 Q3310-102310-401320-202320-501330-302330-601

 

I unpivoted the column to create month number (m1,m2 andm3) but cannot figure out how to create measures for comparison. Please help! Thanks!

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Anonymous , Create a separate table for Period

Period = distinct(union(distinct(sales[Period]), distinct(target[Period])))

 

Add a new column to that table

Period Rank = RANKX(all('Period '),'Period '[Period ],,ASC,Dense)

 

use this for measures like eample. Create new as per need
This Period = CALCULATE(sum('Sales'[M1 Sales]), FILTER(ALL('Period '),'Period '[Period Rank]=max('Period '[Period Rank])))
Last Period = CALCULATE(sum('Target'[M1 Forecast]), FILTER(ALL('Period '),'Period '[Period Rank]=max('Period '[Qtr Rank])-1))

 

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

View solution in original post

Hi, @Anonymous 

You can try follow steps:

1.unpivot your "Sales Table" and "Forecast Table" :

106.png107.png

2. Add a "Period Table" and a "Month table" as below:

104.png105.png

 

3.create calculated columns as below:

1)Last Quarter Comparison

Last Quarter = LOOKUPVALUE('Period Table'[Last Quarter],'Period Table'[Period],'Sales Table'[Period])
Last Quarter Forecast Sales = LOOKUPVALUE('Forecast Table'[Sales],'Forecast Table'[Period],'Sales Table'[Last Quarter],'Forecast Table'[month number],'Sales Table'[month number])
Last Quarter Comparison = 
VAR comp = 'Sales Table'[Sales] & "-" & 'Sales Table'[Last Quarter Forecast Sales]
//var comp = 'Sales Table'[Sales]-'Sales Table'[Last Quarter Forecast Sales]
RETURN
    IF ( ISBLANK ( 'Sales Table'[Last Quarter Forecast Sales] ), BLANK (), comp )

2)Second Last Quarter Comparison

Second Last Quarter month = LOOKUPVALUE('Month Table'[last quarter Month number],'Month Table'[Month number],'Sales Table'[month number])
Second Last Quarter Forecast Sales = LOOKUPVALUE('Forecast Table'[Sales],'Forecast Table'[Period],'Sales Table'[Second Last Quarter],'Forecast Table'[month number],'Sales Table'[Second Last Quarter month])
Second Last Quarter Comparison = 
VAR comp = 'Sales Table'[Sales] & "-" & 'Sales Table'[Second Last Quarter Forecast Sales]
//var comp ='Sales Table'[Sales] -'Sales Table'[Second Last Quarter Forecast Sales]
RETURN
    IF ( ISBLANK ( 'Sales Table'[Second Last Quarter Forecast Sales] ), BLANK (), comp )

 

The result will show as below:

108.png

 

Please check my sample pbix file for more details.

 

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Hi @v-easonf-msft ,

 

Thanks a lot!

Hi, @Anonymous 

Dax 'last quarter' is used for the 'period table'. If  there is an error in this calculated column, please share a screenshot of  the 'period table' you added. 

 

Best Regards,
Community Support Team _ Eason

Anonymous
Not applicable

Hi @v-easonf-msft ,

 

It tried to create calculated columns according to the solutions provided. It successfully calculates the last quarter. However, I encountered difficulties in looking up forecast value. A blank value is shown in the column. Please refer to following picture.

dt1119_0-1615256202470.png

I also uploaded a file for your checking. Grateful if you may take a look and advise any solution. Thanks a lot.

 

https://1drv.ms/u/s!Asj8flgDn0itlC3h8Lz5CUfNX4HF?e=NHAuCl 

amitchandak
Super User
Super User

@Anonymous , Create a separate table for Period

Period = distinct(union(distinct(sales[Period]), distinct(target[Period])))

 

Add a new column to that table

Period Rank = RANKX(all('Period '),'Period '[Period ],,ASC,Dense)

 

use this for measures like eample. Create new as per need
This Period = CALCULATE(sum('Sales'[M1 Sales]), FILTER(ALL('Period '),'Period '[Period Rank]=max('Period '[Period Rank])))
Last Period = CALCULATE(sum('Target'[M1 Forecast]), FILTER(ALL('Period '),'Period '[Period Rank]=max('Period '[Qtr Rank])-1))

 

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Anonymous
Not applicable

Hi @amitchandak ,

 

I tried to created a table called period, which successfully returned distrinct period values. However, when I tried to create a calculated measure for ranking, following alert message is shown.

 

Period Rank = RANKX(all('Period'),'Period'[Period],,ASC,Dense)
 

"A single value for column 'Period' in table 'Period' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

Please have a look. Thanks again!

 

 

@Anonymous , Period Rank is a column, not measure.

 

We will use this in measures this period, last period. 

Anonymous
Not applicable

Hi @amitchandak ,

 

Sorry for the overlook. The measures works successfully. However, if I wish to compare "M1sales" in FY20 Q4 to " M4 forecast" in FY20 Q2, how can I amend the formula? I created a month ID (1,2,3...,5,6)   and corresponding sales and forecast values in the tables. Many thanks!

Hi, @Anonymous 

You can try follow steps:

1.unpivot your "Sales Table" and "Forecast Table" :

106.png107.png

2. Add a "Period Table" and a "Month table" as below:

104.png105.png

 

3.create calculated columns as below:

1)Last Quarter Comparison

Last Quarter = LOOKUPVALUE('Period Table'[Last Quarter],'Period Table'[Period],'Sales Table'[Period])
Last Quarter Forecast Sales = LOOKUPVALUE('Forecast Table'[Sales],'Forecast Table'[Period],'Sales Table'[Last Quarter],'Forecast Table'[month number],'Sales Table'[month number])
Last Quarter Comparison = 
VAR comp = 'Sales Table'[Sales] & "-" & 'Sales Table'[Last Quarter Forecast Sales]
//var comp = 'Sales Table'[Sales]-'Sales Table'[Last Quarter Forecast Sales]
RETURN
    IF ( ISBLANK ( 'Sales Table'[Last Quarter Forecast Sales] ), BLANK (), comp )

2)Second Last Quarter Comparison

Second Last Quarter month = LOOKUPVALUE('Month Table'[last quarter Month number],'Month Table'[Month number],'Sales Table'[month number])
Second Last Quarter Forecast Sales = LOOKUPVALUE('Forecast Table'[Sales],'Forecast Table'[Period],'Sales Table'[Second Last Quarter],'Forecast Table'[month number],'Sales Table'[Second Last Quarter month])
Second Last Quarter Comparison = 
VAR comp = 'Sales Table'[Sales] & "-" & 'Sales Table'[Second Last Quarter Forecast Sales]
//var comp ='Sales Table'[Sales] -'Sales Table'[Second Last Quarter Forecast Sales]
RETURN
    IF ( ISBLANK ( 'Sales Table'[Second Last Quarter Forecast Sales] ), BLANK (), comp )

 

The result will show as below:

108.png

 

Please check my sample pbix file for more details.

 

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.