# Dynamic DAX calculation between two rows of same DAX measure (filtered rows)

#### kalam1989

##### New Member
Background:
I have a measure that calculates Total sales from a master sales data that captures actual sales and forecasted sales on a monthly basis.

Sample data:
And a sample looks like below:

So, the June cycle and July cycle are actual sales upto Jun and Forecasted Sales from Jul to Dec. The figures are calculated from a filter <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Total Sales:=Sum(Sales)</code>, and I'm filtering out only June and July cycle data to see the latest and previous month sales.

And I want to create:

1. A measure to calculate the difference of the two selected months (July and June) in another row to show the variance between two cycles.
2. Another measure to calculate the % of difference (Variance %) between the two selected months in the next row

But I don't want to fix the two months, but rather create a dynamic measure that calculates from the selected filters. For example, I can also see the difference between two other cycles from earlier, or from later cycles.

Is there a way for me to write a dynamic DAX measure to calculate from the results of another DAX measure (but filtered)?

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Matt Allington

##### MrExcel MVP
If the June and July cycle are actual sales up until June, why are the numbers different for periods Jan x Jun?

#### kalam1989

##### New Member
You're right! I forgot to make the figures actuals after I randomized the data!!! Pardon me for overlooking the detail!!

#### kalam1989

##### New Member
Kindly see below for the revised correct numbers:
(Sorry, I'm unable to insert image for some weird reason, so let me attach as a hyperlink below instead)

https://imgur.com/BmX1HAA

The problem I'm facing now is:
I can filter the data based on the cycle (which is in date format) and show the total sales using a measure by month as well. But, I don't know how to do another measure to find the difference between the two filtered rows in the power pivot.

Your help would be greatly appreciated!!
Thank you

1,102,261
Messages
5,485,734
Members
407,511
Latest member
Arunabh

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...