Forum Discussion
Showing Last Measure Values
However, when filtering the data for 11/30/2024, we still want to show Sum of Revenue, Goal % and MonthEndDate for Construction which would be data for a previous period of 10/31/2024 where the solution your provided above would work. For instance, the last data for Construction department was posted for 10/31/2024. When running a report for all departments for 1/31/25, there is no data for construction, but we still would like to show the last avaialble data which was for 10/31/2024.
Thanks for the clarification. It seems the requirement is to infer MonthEndDate when a department is missing MonthEndDate. In the Accounting example, it's a simple matter of adding one month to the latest MonthEndDate. In the Construction example, however, the missing MonthEndDate is between two existing MonthEndDate (the logic would have to look for gaps to determine what MonthEndDate to assign). If there were a scenario where a department has two or more rows with missing MonthEndDate, additional logic would be required to determine which row gets assigned which MonthEndDate.
The cleanest approach would be to correct the data as far upstream as possible (Power Query, SQL). This will simplify the DAX and optimize performance.
