Forum Discussion

Dannyd20's avatar
Dannyd20
Regular Visitor
3 years ago
Solved

Chart requests vs assignments per week

Hi forum,

 

I am struggling with something that I think must be simple, but I can't seem to figure it out! 

I want to use Power Bi to chart requests vs assignments per week.

I have the dates requests were logged and the date they were assigned.

 

I want a chart to show per calendar week, how many requests came in that week and how many were assigned that week ?

 

Thanks

 

some sample data:

 

Logged:
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23
03-Jul-23

 

Assigned:
11-Jul-23
04-Jul-23
11-Jul-23
11-Jul-23
11-Jul-23
11-Jul-23
04-Jul-23
04-Jul-23
11-Jul-23
04-Jul-23
11-Jul-23
11-Jul-23
11-Jul-23
04-Jul-23

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  Dannyd20 ,

     

    Here are the steps you can follow：

    1. Create calculated table.

    You can create a calendar table and use Week as the x-axis in the calendar table.

    Table 2 =
CALENDAR(
    DATE(2023,7,1),
    DATE(2023,7,31))

    Create calculated column.

    Week = WEEKNUM('Table 2'[Date],2)

    2. Create measure.

    Logged_Measure =
COUNTX(
    FILTER(ALL('Table'),
    YEAR('Table'[Logged])=YEAR(MAX('Table 2'[Date]))&&WEEKNUM('Table'[Logged],2)=MAX('Table 2'[Week])),[Logged])
    Assigned_Measure =
COUNTX(
    FILTER(ALL('Table'),
    YEAR('Table'[Assigned])=YEAR(MAX('Table 2'[Date]))&&WEEKNUM('Table'[Assigned],2)=MAX('Table 2'[Week])),[Assigned])

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Dannyd20 ,

     

    Here are the steps you can follow：

    1. Create calculated table.

    You can create a calendar table and use Week as the x-axis in the calendar table.

    Table 2 =
CALENDAR(
    DATE(2023,7,1),
    DATE(2023,7,31))

    Create calculated column.

    Week = WEEKNUM('Table 2'[Date],2)

    2. Create measure.

    Logged_Measure =
COUNTX(
    FILTER(ALL('Table'),
    YEAR('Table'[Logged])=YEAR(MAX('Table 2'[Date]))&&WEEKNUM('Table'[Logged],2)=MAX('Table 2'[Week])),[Logged])
    Assigned_Measure =
COUNTX(
    FILTER(ALL('Table'),
    YEAR('Table'[Assigned])=YEAR(MAX('Table 2'[Date]))&&WEEKNUM('Table'[Assigned],2)=MAX('Table 2'[Week])),[Assigned])

    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