+ Reply to Thread
Results 1 to 2 of 2

DAX Cumulative Measure

  1. #1
    Registered User
    Join Date
    12-06-2017
    Location
    Chicago, IL
    MS-Off Ver
    2016
    Posts
    28

    DAX Cumulative Measure

    Hi, Hoping someone has a magic bullet to help me with a DAX formula.

    I've created a report for a client that has a table that shows budget, forecast, and actuals both by month (columns) and cumulative (lines). The table is built from DAX measures I've created in the data model. It works great, with one exception---the cumulative actuals (the red line). I want the line to stop at the current date. There are no actual yet for future months, and the flat, red line just pisses me off.

    financials chart.JPG

    Here is a screen shot of the table and associated measures in the data model:

    ftable.JPG

    And here is the formula I used to create the "cumulative" measure. It is based on calendar date/year. I have fussed around with it, thinking maybe I could figure out how to have it measure calenader date/month - doesn't work. Or maybe calendar date/today - doesn't work. I know there has to be a way, but I haven't figured it out yet. The report is due Monday....anyone have any ideas??

    dax *** measure.JPG


    Thanks for any thoughts/suggestions!

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,071

    Re: DAX Cumulative Measure

    Just a thought as I've used the OFFSET Function with Excel charts.

    The logic is that the Formula for 'Actuals' would be to COUNTA the populated cells of figures & ignore the future blank cells.

    The purpose of the OFFSET is that it has a parameter width & height and you can use the COUNTA as the width.

    One other idea is that a chart won't display any hidden cells, so could you move your Actuals so it only displays all other data points except Actuals after a certain month. - That's a last resort as it isn't very tidy.

    It would help if you posted an example file.
    Remember you are unique, like everyone else

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to measure 2 results with an IF
    By jimd1768 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-07-2018, 08:00 AM
  2. [SOLVED] Tolerance measure
    By Noppa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-22-2018, 02:47 AM
  3. PowerPivot measure
    By KriZo in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-20-2017, 12:56 PM
  4. Formulas for cumulative value and cumulative %
    By Brotein in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-28-2013, 07:19 PM
  5. [SOLVED] measure one set of coordinates to many
    By lexi521 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-22-2013, 10:53 AM
  6. Excel 2007 : need help - desperate measure...
    By giampo in forum Excel General
    Replies: 1
    Last Post: 08-21-2009, 05:44 PM
  7. units of measure
    By Mitchell in forum Excel General
    Replies: 1
    Last Post: 03-15-2005, 09:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1