Filter by Date then get most recent joined result - Display count of records

[billabang]

Hi 

I am new to Power BI but not to working with data. I am struggling to do something that I could have achieved with other tools so hoping it should be relatively straight forward and just my lack of knowledge in PowerBI that is causing the problem.

I have two tables with the data I need to use, a Main Table and a Sub Table which can be linked by the RecordID.

Main Table Example:

RecordID	Description	Priority
1	Record1	1
2	Record2	1
3	Record3	2
4	Record4	2
5	Record5	3

Sub Table Example

RecordID	Status	Date
1	APPR	01/12/2019
1	COMP	01/01/2020
2	WAPPR	01/11/2019
2	APPR	01/12/2019
2	COMP	01/01/2020
3	APPR	01/01/2020
4	WAPPR	01/12/2019
4	APPR	01/12/2019
4	COMP	01/01/2020
4	CAN	01/01/2020
5	WAPPR	01/01/2020
5	APPR	01/01/2020

I need to display a count of OPEN records (not COMP or CAN status) by priority at any given date - currently just looking to select a month in a slicer/filter and it would show the count based upon the end day of that month.

So if I chose DECEMBER-2019 I should get a bar chart showing that there were 3 projects open (RecordID 1,2 and 4) but seperated into  the bar's for each priority. 

In this case I would see Two records at priority 1 and One record at priority 2.

If this was pure SQL I would simply add a column to the Main Table that looks up the current Status based upon the date filter. I could then count/group the projects by priority. 

I have tried a bunch of different New Measures, joined tables and various bits and pieces from forums that I shall not confuse the matter with here.

Please can someone assist me? I have an example Power BI Desktop file with this data in and a Date Table but cannot see the attachment option as I create this post.

Thanks for any hep - it's much appreciated. I'm looking to understand this as fully as possible so I apologise if any of my follow up questions are a bit naive as far as knowledge of PowerBI goes.

[v-jayw-msft]

Hi @billabang ,

Please check following steps as below.

1# Create a CALENDAR table and a yearmonth calculated column.

CALENDAR = CALENDARAUTO()

yearmonth = FORMAT('CALENDAR'[Date],"YYYYMM")

2# Create a measure.

countrecord = 
CALCULATE (
    DISTINCTCOUNT ( Main[RecordID] ),
    FILTER (
        Sub,
        FORMAT ( Sub[Date], "YYYYMM" ) = SELECTEDVALUE ( 'CALENDAR'[yearmonth] )
            && Sub[Status] <> "COMP"
            && Sub[Status] <> "CAN"
    )
)

 Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[billabang]

Thanks Jay

I failed to provide an example where the project may have been re-opened within a month so I cant just discount COMP altogether. It depends on if the COMP/CAN is the final state in that selected month.

I will try what you have posted anyway as it's good practice for me but do you have any idea how I can only get the LATEST status from the subtable and then perform the count accross the records?

I appreciate your assistance.

[v-jayw-msft · Accepted solution]

Hi @billabang ,

Please modify the Measure countrecord as below.

countrecord = 
CALCULATE (
    DISTINCTCOUNT ( Main[RecordID] ),
    FILTER (
        Sub,
        Sub[Date]
            = CALCULATE (
                MAX ( Sub[Date] ),
                FILTER (
                    ALLEXCEPT ( Sub, Sub[RecordID] ),
                    FORMAT ( Sub[Date], "YYYYMM" ) = SELECTEDVALUE ( 'CALENDAR'[yearmonth] )
                )
            )
            && Sub[Status] <> "COMP"
            && Sub[Status] <> "CAN"
    )
)

 Result would be shown as below.

Pbix as attached.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[billabang]

Thanks - I think that has done the trick!