Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## 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 stop at Feb with total of 2931 but the total gets repeated for the remainder of the year.

Line Graph 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])))

Max Date:= If ([Max Open Date]> [Max Filled Date],[Max Open Date],[Max Filled Date])

Tables

- Opened Reqs

- Filled Reqs

- Forecast Calendar table with past and future dates upto 2022.

1 ACCEPTED SOLUTION
Community Champion

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

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

8 REPLIES 8
Community Champion

@Anonymous

Assuming your x-axis is form your calendar table, try this measure in your line graph:

Measure cut off = IF(MAX(Calendar[Year]) <= YEAR(TODAY()) && MAX(Calendar[Month]) <= MONTH(TODAY()), [your measure], BLANK())

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

Anonymous
Not applicable

@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])))

Anonymous
Not applicable

Guys thanks again for providing feedback.  I was able to resolve the issue by filtering my Forecast Table to only return Year/Month <= Max Date since I don't need to forecast any furture data.

Community Champion

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

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

Anonymous
Not applicable

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 🙂

Community Champion

@Anonymous

No problem! either post or send me a PM

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

Super User

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
Not applicable

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.

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors