Power BI Schedule Dates vs. Actual Date File Received

[PowerQA1]

Hi All,

I am trying to create a visual where I am able to show the schedule date when a file was supposed to be received vs. a calendar date representing a weekly schedule.

This is the view I am looking for:

________________________________________________________________________________________________________________________________________

Desired Results

• I used Excel to show this as an example:
• So I would like to get just the 1 row filled in for if a file was received that week
• If no file was received I would like it to be blank
• This allows us to know which files are missing

_____________________________________________________________________________________________________________________________________

Current Results

• This is the current results we get
• The table visual populates the schedule dates perfectly using the relationship between the date table and schedule table
• But the table visual repeats the field received date into every row
• I would like it to only populate the row for the correct schedule week that the file was received

_______________________________________________________________________________________________________________________________________

Date Table with a Relationship to the Schedule Table

• The schedule table has a day of the week when the file is expected
• By connecting it to the standard Power BI Date table via the day of the week I get the schedule dates of when the weekly file is expected
• But when these two fields are placed into a table visual I get the proper schedule dates but the Date of the 1 file received is repeated in every row.
• Any ideas how to get the results in the Excel example?

Any help is greatly appreciated!!

[amitchandak]

@PowerQA1 , the Date table should only have 1-m Many single-directional join. You should join on the date(in case a date has a timestamp remove that using datevalue)

receviced date = datevalue([received])

You can deal with both dates with inactive join

refer

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[PowerQA1]

Amit thanks so much for your reply.

The first issue is I do not have the actual schedule table.

I know the client information which includes this information:

Customer Key / Index
Client Name
Frequency - In this case weekly
Day - The day the file is expected

So then I need to generate a full schedule for each client.
This would give me the dates files are expected.
So when I connected the date table to the client table by the day of the week I got a full schedule for each customer, which I can show in a table visual, but not in an actual table
_____________________________________________________________________________

My goal is to establish the schedule then compare actual files received vs. the schedule. To see what is missing...

I will look through the rest of your very nice detaile solution but I wanted to share this point.

Again I can't tell you how much I appreciate your help.

I am still some what of a hacker 🙂