Create Power BI DAX Measures for Missing Invoice Dates and Forecast Units
Question details
Create DAX measures that detect missing invoice dates and point IDs, calculate forecast units per day from monthly targets, and return the applicable factor value for each point and date.
- Product
- Microsoft Power BI
- Device & OS
- not provided
- Scenario
- The user is analyzing invoice, calendar, target, point, and factor tables in a Power BI data model.
- Observed behavior
- The report needs calculations that respect the current filter context while matching point IDs, months, target types, and effective factor date ranges.
Confirm that your Calendar table contains every date in the reporting period and is related to the invoice or fact table by date. Verify that point IDs use the same data type across the invoice, target, and factor tables.
Create DAX measures using the Calendar and related model tables
Use this approach when your Power BI model has Calendar, Invoice, Points, Targets, and Factors tables with valid relationships.
Create measures from the Modeling tab and adjust the table and column names to match your data model. For calculations that must honor report filters, use CALCULATE with FILTER, VALUES, and SELECTEDVALUE rather than relying on unfiltered columns.
In Power BI Desktop, select Modeling > New measure and create a measure such as: Missing Invoice Dates = COUNTROWS(EXCEPT(VALUES('Calendar'[Date]), CALCULATETABLE(VALUES('Invoice'[Invoice Date])))). This compares visible calendar dates with visible invoice dates and returns the number of dates that have no invoice.
Select Modeling > New measure and enter: Missing Point IDs = COUNTROWS(EXCEPT(VALUES('Points'[Point ID]), VALUES('Invoice'[Point ID]))). Use the measure in a card or table visual to identify point IDs that exist in the Points table but have no matching invoice in the active filters.
Create a measure from Modeling > New measure: Forecast Units per Day = VAR CurrentPoint = SELECTEDVALUE('Points'[Point ID]) VAR CurrentDate = MAX('Calendar'[Date]) VAR MonthlyTarget = CALCULATE(MAX('Targets'[Target]), FILTER('Targets', 'Targets'[Target Type] = 0 && 'Targets'[Point ID] = CurrentPoint && 'Targets'[Month] = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1))) RETURN DIVIDE(MonthlyTarget, DAY(EOMONTH(CurrentDate, 0))). Replace the target and month columns with your actual fields.
Create a measure using Modeling > New measure: Factor Value = VAR CurrentPoint = SELECTEDVALUE('Points'[Point ID]) VAR CurrentDate = MAX('Calendar'[Date]) RETURN CALCULATE(MAX('Factors'[Factor Value]), FILTER('Factors', 'Factors'[Point ID] = CurrentPoint && 'Factors'[Start Date] <= CurrentDate && 'Factors'[End Date] >= CurrentDate)). This returns the factor whose effective date range includes the current date.
Use TREATAS when point or date tables do not have an active relationship
Use this option when the necessary tables cannot be directly related or when a calculation must apply values from one table as filters on another.
Prepare Forecast and Invoice Data in WPS Spreadsheet
Power BI DAX measures must be created in Power BI, but WPS Office provides a free, lightweight way to prepare the invoice, target, point, and factor source tables before loading them into your BI model. Its familiar spreadsheet interface supports seamless work with common Microsoft Office file formats.
Frequently Asked Questions
How do I show the actual missing invoice dates instead of only a count?
Create a calculated table from Modeling > New table, such as Missing Dates = EXCEPT(VALUES('Calendar'[Date]), CALCULATETABLE(VALUES('Invoice'[Invoice Date]))). Place the resulting date column in a table visual.
Why does the Forecast Units per Day measure return blank?
The measure returns blank when it cannot find one matching target for the selected point, month, and Target Type value of 0. Check the Point ID data types, ensure the month key matches, and confirm that the target record exists.
What should the month column contain for the forecast target lookup?
Use a consistent month key in both the target logic and calendar context, such as the first day of the month created with DATE(YEAR([Date]), MONTH([Date]), 1), or a numeric YYYYMM key. Do not compare a text month name with a date value.
Can I use a relationship instead of FILTER in the factor value measure?
Yes, relate the Points table to Factors by Point ID when the relationship is unambiguous. Because factor records normally use start and end dates rather than one date key, you still need FILTER to test whether the current calendar date falls within the effective range.
How can I handle multiple factor records that overlap on the same date?
Add a factor priority or last-updated column, then use TOPN within the measure to select one record. This makes the result deterministic instead of relying on MAX or MIN across multiple matching values.
