Show Status history of Project with NA for missing dates

[paulgeorge]

I have a requirement to show last five week status history (as on friday) of a project (Filtered in page level) like below

20-Jan-23	27-Jan-23	3-Feb-23	10-Feb-23	17-Feb-23
Green	Yellow	Red		Red

My Status Update table is somehing like this

Status Date	Status	Project
16-Jan-23	Green	Project 1
19-Jan-23	Yellow	Project 1
16-Jan-23	Red	Project 2
31-Jan-23	Green	Project 1
15-Jan-23	Red	Project 2

I created a new custom column "Week" to compute the date of Friday of every week

In Report, I need to show the latest report in a week (ex. for Porject 1, I should show status as on 19 Jan 23). I was able to find the latest report by creating a new custom column called 'Latest Weekly Status'

Latest Weekly Report =
VAR LatestDate =
    MAXX (
        FILTER ('Status Update', 'Status Update'[ProjectID] = EARLIER ( 'Status Update'[ProjectID] ) && 'Status Update'[Week] =  EARLIER('Status Update'[Week])),
        'Status Update'[Report Date]
    )
RETURN IF ('Status Update'[Report Date] = LatestDate, 1, 0)

I am filtering the records which has Latest Weekly Report as 1.
I am struggling to show the weeks where there is no status submitted. Based on the above data in Status table the matrix should be shown like below

For Project 1

20-Jan-23	27-Jan-23	3-Feb-23	10-Feb-23	17-Feb-23
Yellow	NA	Green	NA	NA

and For Project 2

20-Jan-23	27-Jan-23	3-Feb-23	10-Feb-23	17-Feb-23
Red	NA	NA	NA	Red

I created a calendar table with dates and weeks. 
I have the Week and Latest Report Flag in Status Update Table
Can anyone explain for guide me how to use these table and show the report as shown above?
Any suggestions are welcome

[v-yiruan-msft · Accepted solution]

Hi @paulgeorge ,

I created a sample pbix file(see the attachment, please check if that is what you want.

1. Create a separate calendar dimension table(Please DO NOT create any relationship with your fact table)

2. Create a calculated column as below in the calendar table to get the weekdate which is on Friday

Week = 
CALCULATE (
    MIN ( 'Calendar'[Date] ),
    FILTER (
        'Calendar',
        'Calendar'[WeekNum] = EARLIER ( 'Calendar'[WeekNum] )
            && WEEKDAY ( 'Calendar'[Date], 2 ) = 5
    )
)

3. Create a measure as below to get the status

Measure = 
VAR _selweek =
    SELECTEDVALUE ( 'Calendar'[Week] )
VAR _selweeknum =
    WEEKNUM ( _selweek, 2 )
VAR _mindate =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            YEAR ( 'Calendar'[Date] ) = YEAR ( _selweek )
                && 'Calendar'[WeekNum] = _selweeknum
        )
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            YEAR ( 'Calendar'[Date] ) = YEAR ( _selweek )
                && 'Calendar'[WeekNum] = _selweeknum
        )
    )
VAR _status =
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            'Table',
            'Table'[Status Date] >= _mindate
                && 'Table'[Status Date] <= _maxdate
        )
    )
RETURN
    _status

Best Regards