Forum Discussion

jimpatel's avatar
jimpatel
Icon for Post Patron rankPost Patron
2 years ago
Solved

Count of total dates per day

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 

Data Protection
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      jimpatel
      Icon for Post Patron rankPost Patron
      2 years ago

      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's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

     

    then the line chart is simple