topic Running total line until last day from chart in DAX Commands and Tips

Running total line until last day from chart

LiborK — Fri, 23 Sep 2022 13:20:52 GMT

Hello,

I am new in dax.

Problem: I have a table which have 3 columns (ID,TYPE,DATE). ID+TYPE is unique combination.

I create running total measure:

CALCULATE(

DistinctCountID,

FILTER(

ALLSELECTED(DATE,

ISONORAFTER(DATE, MAX(DATE), DESC)

)

The result of the CHART IS:

But I would like to see this:

How can I rewrite the query to be able to see the redline at least a straight, if there will be no data for red TYPE in the last DATE in the chart?

(BTW: I can't create another simple table, because the ID is used in model for filtering)

Thank you in advance.

amitchandak — Sat, 24 Sep 2022 02:31:11 GMT

LiborK , You shoudl use a date table, marked as date table joined with you date of table

CALCULATE(

Distinctcount(Table[ID]) ,

FILTER(

ALLSELECTED('Date') , 'Date'[Date] <= Max('Date'[Date])

)

v-zhangti — Mon, 26 Sep 2022 07:28:49 GMT

You can try the following methods.
Sample data:

Measure = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])))

Is this similar to the output you expect?

Best Regards,

Community Support Team _Charlotte

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

LiborK — Thu, 29 Sep 2022 09:26:55 GMT

Hi, thank you for your answer. I tried your query, bud the result is different:

The data look like this example:

LiborK — Sun, 02 Oct 2022 12:13:17 GMT

Thank you, you were right.