Create a history of a given dataset

[Anonymous]

Hi all,

Probably a noob question here but I'm sure some gurus will have the answer.

I have a dataset about our manufacuring lines that looks like that:

Batch #	Date produced	Target date for records	Actual date for records
1	01/01	15/01	13/01
2	01/01	15/01	10/01
3	02/01	16/01	18/01
4	02/01	16/01	12/01
5	02/01	16/01	20/01

(We have a target of 14 days to complete the records after the batch has been produced)

What I would like to get is to know how many late records are for a given day.

For exemple on 17/01 there was 2 late records (batch 3 & 5).

And then display it on a line chart to see the trend of our backlog. (on X there are the days and on Y the quantity of late records)

I tried to create a separate table on PBI popualted by date and then linked it to the first table but somehow the filters don't seem to work and I'm unable to create a dynamic measure.

I'm not an expert at all in database architecture, anyone has been accross this case ?

Thanks a lot in advance for any answer 🙂

Thomas.

[lbendlin]

" create a separate table on PBI popualted by date and then linked it to the first table"

yes, the first part is correct. No, the second part is incorrect.  This needs to be a disconnected table, and a measure.

[Anonymous]

Thanks a lot for your reply @lbendlin 

Then can I ask how you will see that formula ?

I thought of usine countrows on first table and then use filter to compare values from both table but filter cannot refer to 2 different table.

Thanks,

Thomas.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Sure, here is the cleaned up PBI file: https://www.transfernow.net/dl/20220620OGMH6MNQ/QwOoUtg7

I have created a custom column on the table "Backlog" which is the expected result.

I just don't get how to make it a measure and allow filters on the first table that also sort datas on this table.

Thanks again for your time !

[lbendlin · Accepted solution]

Technically this event (the backlog) is immutable (not influenced by filter choices) so it would be more appropriate to make it a calculated column. Just as you did...

But if you insist you can add the measure too

Backlog = 
var d = max(Backlog[Date])
return CALCULATE(COUNTROWS('Table'),'Table'[Target Records]<=d,'Table'[Actual Records]>d)

see attached

[Anonymous]

Hi Ibendlin,

This measure looks perfect, really thankful for your time and help !

I prefer to keep on a measure so the different users can filters on their own performance.

Thanks again and have a good day,

Thomas.