cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## 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

3 REPLIES 3

@marcorusso Any chance you may have a look?

Community Support

Hi @Grumelo,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

Sample report is shared in the Topic message.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.