Count of total dates per day

[jimpatel]

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 

[v-yangliu-msft · Accepted solution]

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

[jimpatel]

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

[lbendlin]

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

then the line chart is simple