consider selected month is N= Sep, need to calculate N-3 month Quantity and N-6 Month Quantity

[Tharun]

Hi,

Consider the X axis is Date , Y axis is Plan Date, and Value is Prod Qty. in below screenshot

If user select Sep month in slicer then need to display 2820, consider it as N(Sep)= 2820 then need to display the N-1(AUG)=2820, and N-3(July)=2820 and N-6(Nov) = 2400.

calculate the Percenage n-3 diff = ((n-1)-(n-3))/(n-3)

calculate the percentage n-6 diff = ((n-1)-(n-6))/(n-6) 

Can you please help me .

Thanks.

[Anonymous]

Hi @Tharun 

The following testing is for your reference.

My slicer table:

no relationship between two tables

Create several measures as follow

n = 
VAR _index = CALCULATE(SUM(slicer[index]), slicer[Month] = SELECTEDVALUE(slicer[Month]))
RETURN
CALCULATE(SUM([Value]), FILTER('Table', 'Table'[Date].[MonthNo] = _index && 'Table'[Date1].[Month] = SELECTEDVALUE(slicer[Month])))

n - 1 = 
VAR _index = CALCULATE(SUM(slicer[index]), slicer[Month] = SELECTEDVALUE(slicer[Month]))
RETURN
CALCULATE(SUM([Value]), FILTER('Table', 'Table'[Date].[MonthNo] = _index - 1 && 'Table'[Date1].[Month] = SELECTEDVALUE(slicer[Month])))

n - 3 = 
VAR _index = CALCULATE(SUM(slicer[index]), slicer[Month] = SELECTEDVALUE(slicer[Month]))
RETURN
CALCULATE(SUM([Value]), FILTER('Table', 'Table'[Date].[MonthNo] = _index - 3 && 'Table'[Date1].[Month] = SELECTEDVALUE(slicer[Month])))

n - 6 = 
VAR _index = CALCULATE(SUM(slicer[index]), slicer[Month] = SELECTEDVALUE(slicer[Month]))
RETURN
CALCULATE(SUM([Value]), FILTER('Table', 'Table'[Date].[MonthNo] = _index - 6 && 'Table'[Date1].[Month] = SELECTEDVALUE(slicer[Month])))

Percenage n-3 = DIVIDE([n - 1] - [n - 3], [n - 3])

Percenage n-6 = DIVIDE([n - 1] - [n - 6], [n - 6])

Output:

Best Regards,
Yulia Xu

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

[Tharun]

Hi @Anonymous 

Thank you so much this was use full but i need some implementation on this 

can you please help me on this 

Example :- if user select N=May 2023 then need to show like this below screenshot .

calculate the Percenage n-3 diff = ((n-1)-(n-3))/(n-3)

calculate the percentage n-6 diff = ((n-1)-(n-6))/(n-6) 

[Tharun]

Hi,

Thank you so much for solution,

can we create it static like this below screenshot 

[Tharun]

How to create a Slicer table (month and index ) in DAX

[Tharun]

Present the Matrix looks like this