Forum Discussion

Grumelo's avatar
Grumelo
Advocate II
7 years ago

The Missing Values Pattern

I have a list of Projects, each of them have a Start Date.

Each month a Project Report is due for all running Projects (Project Start Date < Period EoM).

 

Business wants to track Projects not having the expected reports.

Data are shown below.

Data Model is as below

 

 

The tricky part is to calculate the measure 'Expected Report'.

 

The expected result look like below

I have tried with the Lost Custormer Approach from the New and Returning Customers pattern.

Without success ...

 

Result so far in power BI, values are wrong, total are wrong

 

Any idea how to solve this challenge ?

 

Please have a look to the shared report

Need Help

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Grumelo,

     

    One sample for your reference.

     

    1. Create two calculated column based on the sample table.

     

    date = DATE(LEFT(Table1[Report Period],4),RIGHT(Table1[Report Period],2),1)
    Periddate = EOMONTH(Table1[date],0)

    2. Create a measure to achieve the goal.

     

    Excepted result = IF(MAX(Table1[Starting Date])<=MAX(Table1[Periddate]),1,0)

    3. Also we can use a calculated column to work on it.

     

    Excepted = IF(Table1[Starting Date]<=Table1[Periddate],1,0)

     

    For more detials, please check the pbix as attached.

     

    Regards,

    Frank

    • Grumelo's avatar
      Grumelo
      Advocate II
      7 years ago

      Data can't be manipulated, data model (edited original post) can't change.

       

      Sample report is shared in the Topic message.