# 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 is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### 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

### Forum statistics

1,106,186
Messages
5,509,691
Members
408,749
Latest member
Bhuvaneshvar kashyap

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...