Forum Discussion

mcobelens's avatar
mcobelens
Frequent Visitor
7 years ago
Solved

Pivot table about missing data

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...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

     

     

MFelix's avatar
MFelix
Super User
7 years ago

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's avatar
    mcobelens
    Frequent Visitor
    7 years ago

     Thanks, works like a charm!