Comparison between 2 Categories with Dynamic Slicers

[Anonymous]

Hello,

I am looking for some help with one of the Reports that i am working on.

The report basically compares the values in 1 Table between different Periods. Here Period is in Text formt (Example : FY20Q1, FY20Q2,...) The Data sits one below the other and the size of data if around 30 Million rows.

I am looking for a help with Data Comparison between different periods and it should give Observations post Comparison. The Observation should in turn be a part of Slicer as well.

The data will be as below:

Input:	Period	SA Number	Part Number	Price	Qty
	FY20Q1	SA1	PN1	0.5	2
	FY20Q1	SA1	PN2	0.4	3
	FY20Q1	SA1	PN3	0.9	3
	FY20Q1	SA1	PN4	0.2	3
	FY20Q1	SA2	PN1	1.2	1
	FY20Q1	SA2	PN2	0.3	3
	FY20Q2	SA1	PN1	0.5	5
	FY20Q2	SA1	PN2	0.4	2
	FY20Q2	SA1	PN4	0.2	3
	FY20Q2	SA1	PN5	0.9	3
	FY20Q2	SA3	PN1	1.2	1
	FY20Q2	SA3	PN2	0.3	3

and the expected Output is as below:

			FY20Q1	FY20Q2
Output:			QPA	QPA	Observation
FY20Q1 Vs FY20Q2	SA1	PN1	2	5	QPA Increased
		PN2	3	2	QPA Decreased
		PN3	3		PN Deleted
		PN4	3	3	No Change in QPA
		PN5		3	PN added

There should be an option to compare between any selected period (Example : FY20Q1 vs FY20Q2 , FY20Q1 Vs FY20Q4 and so on)

Request all the Power BI experts to help me achieve this.

Thanks and Regards,

Mohseen

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can create two slicers based on two new tables and three measures to meet your requirement.

1. Create two new tables just containing the distinct period.

Slicer 1 = DISTINCT('Table'[Period])

Slicer 2 = DISTINCT('Table'[Period])

2. Create three measures and put them to a table visual.

QPA1 = 
var _selected = SELECTEDVALUE('Slicer 1'[Period 1])
return
CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[Period]=_selected))

QPA2 = 
var _selected = SELECTEDVALUE('Slicer 2'[Period 2])
return
CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[Period]=_selected))

Observation = 
IF(
    ISBLANK([QPA2]),"PN Deleted",
    IF(
        ISBLANK([QPA1]),"PN added",
        IF(
            [QPA1]<[QPA2],"QPA Increased","QPA Decreased")))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You need to two period tables. One can be connected. And then refer this blog. Here I have used the date range. You can use period value. In place on Min and Max and use only Max.  And filter = max value

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601