Forum Discussion

Grumelo's avatar
Grumelo
Advocate II
7 years ago

The Missing Values Pattern

Origianl Topic is there   https://community.powerbi.com/t5/Desktop/The-Missing-Values-Pattern/td-p/538927   Sorry for the confusion, post has been duplicated
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

Am I missing something or is it as simple as, if Report Period is less than Project Starting Date then blank, otherwise 1?

  • Grumelo's avatar
    Grumelo
    Advocate II
    7 years ago

    It may works in the first table but total will be wrong and no correct answer in second table when no Period context is defined.

    Dealing wih a calculated column won't help as well because we are dealing with data that does not exist.

    Look carefuly to the available data at the top.

     

    • v-danhe-msft's avatar
      v-danhe-msft
      Microsoft Employee
      7 years ago

      Hi Grumelo,

      Based on my test, you could refer to below steps:

      Sample data:

      Create two measures:

      Expected reports measure = IF(MONTH(MAX('Table1'[Starting Date]))>MONTH(MAX('Table1'[Report period])),BLANK(),1)
      Measure = SUMX('Table1','Table1'[Expected reports measure])

      Result:

      You could also download the pbix file to have a view.

       

      Regards,

      Daniel He

      • Grumelo's avatar
        Grumelo
        Advocate II
        7 years ago

        Look at my data model, there is no such table combining all projects and all periods. That would be too simple.

        We are dealing with missing data, missing records.

         

        Project P30 has no records at all in the Project Report Table and I'm trying to calculate how many are expected.