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

kalam1989

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

Your advice is greatly appreciated.
 

Some videos you may like

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
Joined
Dec 18, 2014
Messages
1,227
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
Joined
Jun 25, 2015
Messages
5
You're right! I forgot to make the figures actuals after I randomized the data!!! Pardon me for overlooking the detail!!
 

kalam1989

New Member
Joined
Jun 25, 2015
Messages
5
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 :)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
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...
Top