topic Re: Visual returning the same data for every date when using CALENDARAUTO() in DAX Commands and Tips

Visual returning the same data for every date when using CALENDARAUTO()

OhLookAnError — Mon, 14 Nov 2022 20:00:29 GMT

I am counting rows from "tblA" under two different conditions using these measures:

Cnt of A = COUNTROWS(Filter(tblA, tblA[Column Name] = "A"))

Cnt of B = COUNTROWS(Filter(tblA, tblA[Column Name] = "B"))

Dropping these into a clustered column and when I use the Date from "tblA" I get the correct occurrences for both in each month but I am learning that because the dates in "tblA" are not continuous it prohibits me from using a trendline. When I drop my CALENDARAUTO() calendar into the X-axis it gives me the same values in every month.

My "Date" from my Calendar Table where the CALENDARAUTO() column is has a one to many relationship with my date column in "tblA" and the cross filtering is set to both. Any help or guidance would be greatly appreciated!

Re: Visual returning the same data for every date when using CALENDARAUTO()

FreemanZ — Mon, 14 Nov 2022 23:07:04 GMT

what do you expect?

Re: Visual returning the same data for every date when using CALENDARAUTO()

OhLookAnError — Tue, 15 Nov 2022 00:31:22 GMT

FreemanZ I Have a continuous calendar table and I have a calendar generated by the random dates of "tblA". When I use the dates out of tblA I get the correct data in each month but when I use the calendar table I get what might be a sum of all the data in each month. normally I wouldnt care and would just use the calendar out of tblA but I need a trendline and my understanding is that since that is not a continuous calendar, it won't give me the option for a trendline.

Re: Visual returning the same data for every date when using CALENDARAUTO()

Anonymous — Tue, 15 Nov 2022 08:25:30 GMT

Hi OhLookAnError ,

As far as I know, trendline should work in cluster column which contains continuous date.

Below is my sample table which doesn't have continuous date like 'tblA'.

According to your statement, I think you may use date hierarchy in your visual. Then trendline will not work in analysis pane. Here I suggest you to add [Date] column whether from "Calendar" or "tblA" into X axis field and select X axis type as Continuous.

For reference: Use the Analytics pane in Power BI Desktop

Best Regards,
Rico Zhou

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

Re: Visual returning the same data for every date when using CALENDARAUTO()

OhLookAnError — Tue, 15 Nov 2022 13:44:04 GMT

Thank you!