topic Re: Column chart with two different date for the same month in DAX Commands and Tips

Column chart with two different date for the same month

zhona9 — Wed, 03 May 2023 06:50:07 GMT

I would like to make a stacked column chart that reports the number of incidents closed in a month, basis the incident sign-off date and the number of incidents reported in a month, basis the incident reported date. The Y-axis will the be number of incidents and the X-axis is the month-year of the past 12 months.

The tables are imported using Direct Query as the dataset is large. Hence I am unable to do any calculated columns, only DAX measures.

Is this possible?

Re: Column chart with two different date for the same month

johnt75 — Wed, 03 May 2023 08:14:19 GMT

Create 2 relationships from your date table to your fact table, one based on the sign off date and one based on the reported date. You can then activate the correct relationship in the measures like

Num reported = CALCULATE ( COUNTROWS ( 'Table' ), USERELATIONSHIP ( 'Date'[Date], 'Table'[Reported date] ) )

Re: Column chart with two different date for the same month

zhona9 — Mon, 08 May 2023 07:42:22 GMT

Do you have a link to how to create a date table?

I found this code, but not sure if it is correct.

Dates = CALENDAR(MIN('g360_Incidents'[reported_date]),MAX('g360_Incidents'[risk_lead_sign_off_date]))

I get an error:

Re: Column chart with two different date for the same month

johnt75 — Tue, 09 May 2023 08:14:34 GMT

https://bravo.bi/ can create date tables for you. The syntax you used is correct, not sure why you're getting an error. Maybe some of the dates in the columns you referenced are blank.

Re: Column chart with two different date for the same month

zhona9 — Thu, 11 May 2023 02:29:26 GMT

In the end I used the reported date for min and max in the date table and it was ok. You are right, the sign off date will be blank at times and that caused the error.