cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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)

Thanks.

5 REPLIES 5
Community Support

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.

Frequent Visitor

Thank you so much this was use full but i need some implementation 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)

Frequent Visitor

Hi,

Thank you so much for solution,

can we create it static like this below screenshot

Frequent Visitor

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

Frequent Visitor

Present the Matrix looks like this

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors