Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Line Charts with Cumulative values

Hello

 

Firstly, I'm only a beginner with Power BI so please forgive me if this is a silly question.

 

I'm graphing my actual expenditure to date as a cumulative total however the line continues past the current month showing the current month's value. I’m wanting the line to end as at the current month.

 

Is there a setting in Power BI that can fix this or could someone please assist with amending the measure?

 

Cumulative payments = CALCULATE(SUM('Money Transfers 201819'[MT amount for charts]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

 

 

Thank you in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Give this a try:

    Cumulative payments = var contextDate = MAX('Calendar'[Date])
var output = CALCULATE(
	SUM('Money Transfers 201819'[MT amount for charts]),
	FILTER(ALL('Calendar'[Date]),
	'Calendar'[Date] <= contextDate)
)
RETURN
IF(
	contextDate <= Today(),
	output
)

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Give this a try:

    Cumulative payments = var contextDate = MAX('Calendar'[Date])
var output = CALCULATE(
	SUM('Money Transfers 201819'[MT amount for charts]),
	FILTER(ALL('Calendar'[Date]),
	'Calendar'[Date] <= contextDate)
)
RETURN
IF(
	contextDate <= Today(),
	output
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks so much, worked perfectly!