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

[Anonymous]

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

Period	M1 Sales	M2 Sales	M3 Sales
FY20 Q1	110	120	130
FY20 Q2	210	220	230
FY20 Q3	310	320	330

Forecast Table

Period	M1 Forecast	M2 Forecast	M3 Forecast	M4 Forecast	M5 Forecast	M6 Forecast
FY20 Q1	101	201	301	401	501	601
FY20 Q2	102	202	302	402	502	602
FY20 Q3	103	203	303	403	503	603

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.

Period	M1	M2	M3
Last Quarter Comparison	Second Last Compariosn	Last Quarter Comparison	Second Last Compariosn	Last Quarter Comparison	Second Last Compariosn
FY20 Q2	210-101		220-201		230-301
FY20 Q3	310-102	310-401	320-202	320-501	330-302	330-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!

[amitchandak · Accepted solution]

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

Hi @v-easonf-msft ,

Thanks a lot!

[v-easonf-msft]

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]

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.

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 · Accepted solution]

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

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!

[amitchandak]

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

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

[Anonymous]

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!

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You can try follow steps:

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

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

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:

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.