Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PowerQA1
Frequent Visitor

Power BI Schedule Dates vs. Actual Date File Received

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

 

PowerQA1_0-1679844388560.png

_____________________________________________________________________________________________________________________________________

 

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

 

PowerQA1_1-1679844388565.png

_______________________________________________________________________________________________________________________________________

 

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?

PowerQA1_0-1679844846863.png

Any help is greatly appreciated!!

 
2 REPLIES 2
amitchandak
Super User
Super User

@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...

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 🙂

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.