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 am trying to get the last non blank value from a table of entries by month. The issue I am having is I can't get the grand total to work correctly
Estimated Cost @@ Completion - Last Reported = var minDate = MIN('Calendar'[Date]) var totalCost = CALCULATE ( (SUM ( 'Work In Progress'[Cost] ) + SUM('Work In Progress'[CostAdjustment])), LASTNONBLANK ( FILTER(ALL(Calendar[Date]), 'Calendar'[Date] <= minDate), CALCULATE ( SUM ( 'Work In Progress'[Cost] ) + SUM ( 'Work In Progress'[CostAdjustment] ) ) ) ) return totalCost
I am using the minimum date because I am trying to calculate the last reported value for the selected period.
Any help would be greatly appreciated
Hi @ghunt03,
Can you share the file? Please mask the private parts first.
Why did you use LASTNONBLANK?
Maybe the formula could work.
Estimated Cost @@ Completion - Last Reported = var minDate = MIN('Calendar'[Date]) var totalCost = LASTNONBLANK ( FILTER(ALL(Calendar[Date]), 'Calendar'[Date] <= minDate), SUM ( 'Work In Progress'[Cost] ) + SUM ( 'Work In Progress'[CostAdjustment] ) ) return totalCost
Best Regards,
Dale
I was using the LASTNONBLANK formula to find the latest reported value. The formula you suggested only seems to return a date
I have a copy of the file available at the following link
The other option I have tried is to lookup the Cost and Cost Adjusment value as there is only a maximum of one entry per month. However I haven't been able to figure out the correct syntax for this one either
Hi @ghunt03,
I wonder if the solution below could help.
1. Create a new table "Contract" due to not all the contract exists in all the months.
Contract = DISTINCT(VALUES('Work In Progress'[Contract]))
2. Create a calculated table.
Result = SUMMARIZECOLUMNS ( Contract[Contract], 'Work In Progress'[Mth], "value", VAR total = SUM ( 'Work In Progress'[Cost] ) + SUM ( 'Work In Progress'[CostAdjustment] ) RETURN IF ( ISBLANK ( total ), CALCULATE ( ( SUM ( 'Work In Progress'[Cost] ) + SUM ( 'Work In Progress'[CostAdjustment] ) ), LASTNONBLANK ( FILTER ( ALL ( 'Work In Progress'[Mth] ), 'Work In Progress'[Mth] <= [Mth] ), CALCULATE ( SUM ( 'Work In Progress'[Cost] ) + SUM ( 'Work In Progress'[CostAdjustment] ) ) ) ), total ) )
3. Create a visual based on the new table.
Best Regards,
Dale
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |