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

     

     

amitchandak's avatar
amitchandak
Super User
6 years ago

Add one more condition

 

Max Date:= 

var _max = If ([Max Open Date]> [Max Filled Date],[Max Open Date],[Max Filled Date])
return 
if(_max>today(),today(),_max)

Or 


Max Date:= 

var _max = If ([Max Open Date]> [Max Filled Date],[Max Open Date],[Max Filled Date])
return 
if(_max>eomonth(today(),0),eomonth(today(),0),_max)

 

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak 

Hi

 

Thanks for the feedback.  That did not work.  I tired both the suggestions and updated the Max Date calc.  My line graph still displays cumulative value for future months.