topic Re: Count of total dates per day in Developer

Count of total dates per day

jimpatel — Wed, 06 Mar 2024 09:39:21 GMT

Hi,

Thanks a lot for looking at my post.

I would like to do the below logic in power bi, Any idea will be much appreciated.

I have "Item","Date1" and "Date2" column and i would like to get result in "Number of Date1" and "Number of Date 2" please. The logic is i wanted to count how many date1 and date 2 is falling in there respective date. I would like to create chart something like that as well.

Any idea please?

Thanks a lot

Re: Count of total dates per day

lbendlin — Wed, 06 Mar 2024 22:24:35 GMT

You would want to rearrange your data into a usable format, for example

then the line chart is simple

Re: Count of total dates per day

Anonymous — Thu, 07 Mar 2024 06:50:01 GMT

Hi @jimpatel ,

Here are the steps you can follow：

1. Create calculated table – this column is the X-axis, which can contain all the de-emphasized data of Date1 and Date2.

Table 2 = var _table= DISTINCT('Table'[Date1]) var _table2= DISTINCT('Table'[Date2]) var _table3= UNION(_table,_table2) return CALENDAR( MINX(_table3,[Date1]),MAXX(_table3,[Date1]))

2. Create measure.

Number of Date1 = COUNTX( FILTER(ALL('Table'),'Table'[Date1]=MAX('Table 2'[Date])),[Item])

Number of Date2 = COUNTX( FILTER(ALL('Table'), 'Table'[Date2]=MAX('Table 2'[Date])),[Item])

3. Result:

Best Regards,

Liu Yang

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

Re: Count of total dates per day

jimpatel — Thu, 07 Mar 2024 10:54:47 GMT

Sorry for reopening this topic.

DAX formula works great and thanks a lot for that.

Is there any way we can use it in the same table instead of creating new table. I have several columns in "Table" which needs to be added in "Table2". That is i have created a "Table view" underneath the graph and it wont work by clicking the graph as both are seperate table.

Any idea please

thanks a lot