Plot cumulative line up to the last date with data

[CarlosOlmos29]

Hello Power BI community.

I am trying to make a cumulative line chart, but I need that one of the lines does not extend as long as possible, but cut off when I have no more data. I am attaching a graph to give more clarity to the question. In a few words, I need the green line to go until March 31, 2023 and from there the yellow line starts. Thank you very much in advance for your help.

[Ashish_Mathur]

Hi,

The solution would depend upon the data structure.  Share the download link of the PBI file and if possible, please share column/table names in English.  Also, since i see multiple lines in the chart, i am not clear of your expected result.  You may please show the expected result in a Table format.  From there, we should be able to create your desired chart.

[CarlosOlmos29]

Hi man,

This is the structure of the table I want to show and below is the chart that I would like to achive. In a few words, the lines are cumulatives (green is real, yellow is forecast), so I would like the green line goes until february and since there the yellow line starts (march). 

Thanks in advance for your help. I hope I have made myself clear. 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Anonymous]

Hi @CarlosOlmos29 

You can create a measure

e.g 

Measure=IF(OR(SELECTEDVALUE('Table'[Type])="AcumWOSECREAL"&&SELECTEDVALUE('Table'[DATE])<=DATE(2023,3,31),SELECTEDVALUE('Table'[Type])<>"AcumWOSECREAL"),1,0)

Then put the measure to the visual filter

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CarlosOlmos29]

Hi! Thank you for taking the time to answer me.

This was the measure I created:

Measure = IF(OR(SELECTEDVALUE('Actividad Real CA'[WO Secundaria Real]) = "AcumWOSecReal" && SELECTEDVALUE('Actividad Real CA'[Fecha]) <= DATE(2023,03,31), SELECTEDVALUE('Actividad Real CA'[WO Secundaria Real]) <> "AcumWOSecReal"),1,0) and I got this error:

[Anonymous]

Hi @CarlosOlmos29 

Is the [Fecha ]field column  a date type? follow the error prompt above, this [Fecha].The data type may be text, so it cannot be compared with the following 2023/3/31 (numeric column), you need to use the date column to compare with the date 2023/3/31.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CarlosOlmos29]

Hi Yolo,

Yes, it is. The Fecha field column is a date type. The other fields are cumulative measures.

This is the structure of the table I want to show and below is the chart that I would like to achive. In a few words, the lines are cumulatives (green is real, yellow is forecast), so I would like the green line goes until february and since there the yellow line starts (march). 

Thanks in advance for your help. I hope I have made myself clear. 

[Anonymous · Accepted solution]

Hi @CarlosOlmos29 

You can refer to the following example.

I make some change on sample data.

Sample data 

Measure

Measure = CALCULATE(SUM('Table'[A]),FILTER(ALLSELECTED('Table'),[Date]<=MAX('Table'[Date])&&MAX('Table'[Date])<=DATE(2023,3,31)))

Measure 2 = CALCULATE(SUM('Table'[B]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])&&MAX('Table'[Date])>=DATE(2023,3,31)))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CarlosOlmos29]

It works perfect! Thanks you very much!