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


New Member
Jun 25, 2015
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)?

Your advice is greatly appreciated.

Some videos you may like

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
Dec 18, 2014
If the June and July cycle are actual sales up until June, why are the numbers different for periods Jan x Jun?


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


New Member
Jun 25, 2015
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)


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 :)

Watch MrExcel Video

Forum statistics

Latest member

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...
    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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...