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