Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Data can't be manipulated, data model (edited original post) can't change.
Sample report is shared in the Topic message.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |