Pivot table about missing data

[mcobelens]

Hi,

I'm struggling with the following. I have to create a pivot table with information about clients that have send in data. Clients have to send in data every month and the table has to show per client information about who has send in the data, who hasn't and who hasn't but didn't have to.

Each client has a begin and end date, if the end date was for example feb 2018, then the table of 2018 has to show if he did send in his data in jan. and feb. but for the remaining months it has to show an x

So lets say we have the following model:

Clients

Id                       Name                         BeginDate                 EndDate

------------------------------------------------------------------------------------

1                        Joe                              2014-01-01

2                        Bill                              2018-02-02              

3                        Jill                               2016-06-02              2017-05-01

4                        Fred                                                             2018-02-01

Reports

----------------------------------------------------------------------------------

ClientId             Total                         Date

2                        50                            2018-01-02

2                        25                            2018-02-14

2                        100                          2018-04-09

4                         60                           2018-01-04

Calender

-------------------------------------------------------------------------------------

Date

MonthName

Year

And the report has to look somthing like this

Joe      -         -         -            -        -

Bill       v        v         -            -        v

Jill        x        x         x           x         x 

Fred     v        -         x           x         x

           jan    feb    march    may   april

where v = received, - = missing and x = not applicable

I did manage to get the report with on received and missing, but I have no idea how to add the not applicable?

[mcobelens]

Hi,

I'm struggling with the following. I have to create a pivot table with information about clients that have send in data. Clients have to send in data every month and the table has to show per client information about who has send in the data, who hasn't and who hasn't but didn't have to.

Each client has a begin and end date, if the end date was for example feb 2018, then the table of 2018 has to show if he did send in his data in jan. and feb. but for the remaining months it has to show an x

So lets say we have the following model:

Clients

Id                       Name                         BeginDate                 EndDate

------------------------------------------------------------------------------------

1                        Joe                              2014-01-01

2                        Bill                              2018-02-02              

3                        Jill                               2016-06-02              2017-05-01

4                        Fred                                                             2018-02-01

Reports

----------------------------------------------------------------------------------

ClientId             Total                         Date

2                        50                            2018-01-02

2                        25                            2018-02-14

2                        100                          2018-04-09

4                         60                           2018-01-04

Calender

-------------------------------------------------------------------------------------

Date

MonthName

Year

And the report has to look somthing like this

Joe      -         -         -            -        -

Bill       v        v         -            -        v

Jill        x        x         x           x         x 

Fred     v        -         x           x         x

           jan    feb    march    may   april

where v = received, - = missing and x = not applicable

I did manage to get the report with on received and missing, but I have no idea how to add the not applicable?

[MFelix · Accepted solution]

Hi @mcobelens,

Try the following measure:

delivery Reports =
IF (
    MAX ( 'Calendar'[Date] )
        <= IF (
            MAX ( Clients[EndDate] ) = BLANK ();
            TODAY ();
            EOMONTH ( MAX ( Clients[EndDate] ); 0 )
        )
        && MAX ( 'Calendar'[Date] )
            >= IF (
                MAX ( Clients[BeginDate] ) = BLANK ();
                DATE ( 1900; 1; 1 );
                MAX ( Clients[EndDate] )
            );
    IF ( COUNT ( Reports[Client ID] ) = BLANK (); "-"; "V" );
    "X"
)

As you can see result below:

Attach PBIX File.

Regards,

MFelix

[mcobelens]

 Thanks, works like a charm!