How to get rid of straight line after today () in line chart based on measures

[jsha]

Hello

I have a document table which contains about 25 000 documents. This table contains columns for planned issue dates and actual issue dates.

My goal is to build a line chart with one line for number of planned document issues and one line for number of actual documents issued (sliceable on discipline).

This is what I have done so far:

1. Made separate date table

2. Set relationships between original document table and date table (planned issue date & date) (active)

3. Set relationships between original document table and date table (actual issue date & date) (inactive)

4. Made measure for count of planned issues per day in date table (CALCULATE/COUNTA/ALLSELECTED)

5. Made measure for count of actual issues per day in date table (CALCULATE/COUNTA/USERELATIONSHIP/ALLSELECTED)

6. Made measure for running total of the planned issues in step 4 (CALCULATE/SUMX/FILTER/ALLSELECTED/ISONORAFTER)

7. Made measure for running total of the actual issues in step 5 (CALCULATE/SUMX/USERELATIONSHIP/FILTER/ALLSELECTED/ISONORAFTER)

The problem is that I cannot get my running total actual curve (step 7) to end at today(). If sentence involving today() and blank () will not do the trick.

Any suggestions?

[v-lili6-msft · Accepted solution]

hi, @jsha

After my research, you may try to improve your formula as below:

actual = var _maxdate=IF(CALCULATE(MAX(Table1[Date]))<=TODAY(),MAX(Table1[Date]),BLANK()) return

CALCULATE(SUMX(Table1,[Sales]),FILTER(ALLSELECTED(Table1[Date]),ISONORAFTER(Table1[Date],_maxdate,DESC))) 

Result:

Best Regards,
Lin

[jsha]

Excellent. Thank you!