Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Stopping Line Graph to current month

I have a line graph that calculates a cumulative total from previous year and continues the total to the new year.  I'm having trouble stopping the measure to the current month.  I need the line to s...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    6 years ago

    Anonymous 

    Apologies for that...The variables aren't what you need.

    Do you have a YearMonth column in your Calendar Table? (if not, please add one using the "following" DAX:

    YearMonth = Calendar[Year] *100 + Calendar[Month Number]

    and format it as a whole number.

     

    For illustration purposes, I'm using a dummy model, so please adapt to yours:

    Cumulative measure (no need to change yours):

    Cumulative Forecast = CALCULATE([Sum of Forecast];
                    FILTER(ALL('Calendar Table');
                        'Calendar Table'[date] <= MAX('Calendar Table'[date])))

     

    Now you can use the following measure to cut the values off at the current month:

    Cut off Forecast = 
VAR calc = FORMAT(YEAR(TODAY()) *100 + MONTH(TODAY()); "0000")
Return
IF(MAX('Calendar Table'[YearMonth]) <= calc; [Cumulative Forecast]; BLANK())

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

PaulDBrown  That solution did not work either.  Could the issue be in the main measure that caluclates the cumulative total?  Based on the data in the table, the cumulative measure displays the correct total.

 

Cumulative Measure:  CALCULATE(SUM('Opened Reqs'[OPEN_BY_MONTH]) + sum('Filled Reqs'[FILL_CLOSE_BY_MONTH]),
CALCULATETABLE(DATESBETWEEN('Forecast Calendar'[Date],"1/1/2015",[Max Date])))

 

 

PaulDBrown's avatar
PaulDBrown
Community Champion
6 years ago

Anonymous 

Apologies for that...The variables aren't what you need.

Do you have a YearMonth column in your Calendar Table? (if not, please add one using the "following" DAX:

YearMonth = Calendar[Year] *100 + Calendar[Month Number]

and format it as a whole number.

 

For illustration purposes, I'm using a dummy model, so please adapt to yours:

Cumulative measure (no need to change yours):

Cumulative Forecast = CALCULATE([Sum of Forecast];
                    FILTER(ALL('Calendar Table');
                        'Calendar Table'[date] <= MAX('Calendar Table'[date])))

 

Now you can use the following measure to cut the values off at the current month:

Cut off Forecast = 
VAR calc = FORMAT(YEAR(TODAY()) *100 + MONTH(TODAY()); "0000")
Return
IF(MAX('Calendar Table'[YearMonth]) <= calc; [Cumulative Forecast]; BLANK())

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi PaulDBrown ,

    Could you help show me the pbxi file?

    I have exact same need ,

    cutting off running total, until todays date.

     

    However I tried your suggestion, it does not work. (The measure shows just blank , instead of cut off).

    I will create a new ticket , and invite you for answer.

    Hope it is Ok! 

    Thanks 🙂

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      6 years ago

      Anonymous 

      No problem! either post or send me a PM