Dynamic DAX calculation between two rows of same DAX measure (filtered rows)
Results 1 to 4 of 4

Thread: Dynamic DAX calculation between two rows of same DAX measure (filtered rows)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 Total Sales:=Sum(Sales), 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.

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    If the June and July cycle are actual sales up until June, why are the numbers different for periods Jan x Jun?
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    New Member
    Join Date
    Jun 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  4. #4
    New Member
    Join Date
    Jun 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •