topic Re: How do I count rows that consist of dates into a line graph? And possible even make a forecast? in DAX Commands and Tips

How do I count rows that consist of dates into a line graph? And possible even make a forecast?

Anonymous — Mon, 08 Aug 2022 16:17:18 GMT

I would like to have the start date (the amount of the same dates, counting) in a line graph as well as the recent start date (the amcount of the same dates, counting)

For example, in the line graph I would like to have all the dates that relate tot september 2020 in one point of the graph, as well as the other months. Do I make an index column to give the rows a number in order to make the graph? How do I make this graph?

Note: I am new to Power BI and English is not my first language. Thank you in advance.

Re: How do I count rows that consist of dates into a line graph? And possible even make a forecast?

johnt75 — Tue, 09 Aug 2022 10:50:25 GMT

Create a proper Date table and then create 2 relationships from the Date table to your current table, one linking to the start date and one linking to the recent start date. You can make both relationships inactive.

Create 2 measures like

Num rows using start date = CALCULATE( COUNTROWS( 'Table' ), USERELATIONSHIP( 'Date'[Date], 'Table'[Start date]) ) Num rows using recent start date = CALCULATE( COUNTROWS( 'Table' ), USERELATIONSHIP( 'Date'[Date], 'Table'[Recent start date]) )

Now you can put columns from your Date table into a line chart and add the new measures.

Re: How do I count rows that consist of dates into a line graph? And possible even make a forecast?

RicoZhou2 — Thu, 11 Aug 2022 08:18:52 GMT

Hi Anonymous ,

In addition to johnt75's reply, you can try CALENDAR() OR CALENDARAUTO() function to create a DimDate table.

DimDate = ADDCOLUMNS( CALENDARAUTO() ,"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))

Then create relationships and measures as he mentioned above.

Result is as below.

Best Regards.