Showing related records and filtering on a dynamic number of days

[Dayna]

Hi all,

I'm reasonably new to PowerBI and I'm trying to develop what I think is a fairly simple report, but I'm having real difficulty in even knowing where to start with it and I'd appreciate your help. Effectively, I'm trying to make a report for a stock take, which shows whether we've counted the stock within a time period selected by the user.

What I want to be able to do is select a date from the Inventory Snapshot table, and then specify the number of days to look back from (i.e. 30) and see whether there's a record that matches it from the Transaction Counted table.

So, for Product X, I'd select an Inventory date record of the 3rd of December, the user somehow enters 45 days as an example (help - how do you allow for parameter data to be entered into the report??) and then I'd want to look in the Transaction Counted table 45 days previous from the 3rd of December, and if I find any record, put a flag on it to say it has been found, otherwise, not found.

I have two tables:

1) Inventory Snapshot Records

2) Transaction Counted Records

Both of these tables have common data which is how it's linked. At present, I've made a master table which was the two tables apphended together and then the duplicates removed with all these records. I join table 1) and table 2) to to master table. These common fields are:

• Product
• Pallet Reference
• Lot Reference
• Site

I join the master table to Table 1 using a join of Product / Pallet Reference / Lot Reference / Site. I do the same from the master table to Table 2.

I use the date field on the Inventory table to specify the date I want to look back from. But this is where I'm now stuck.

1) How do I allow the user to specify a numeric range of days into the report?

2) How do I filter the records on the Counted Transactions to look at the date selected from the Inventory table, and then X days previous to this based on the numeric range they've selected?

3) Then I need to work out how to say whether it found a record, or not...

Hopefully I've been fairly clear on the requirements, but as you can probably tell, I'm struggling to know where to start on this. 

This report will be ran for multiple products / references, so it'll bring back a big list of records based on a date range, so I can't narrow it down to one product / pallet. 

I appreciate any help that you can offer, and please let me know if I should add more detail to try and explain my requirements a little further.

Much appreciated,

Dayna

[v-zhangti]

Hi, @Dayna 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dayna]

I managed to resolve this with the use of parameters and measures in the report, but thanks for following up.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...