Forum Discussion
Latest Budget Version based on date filtering
We are looking for the latest budget based on a budget version and date filtering.
Consider the following dataset:
Budget Table (related to a date dimension)
| Project Name | Subtask | Budget Version | Budget | Date |
| X | sub01 | 1 | 100 | 1-jan |
| X | sub02 | 1 | 200 | 1-jan |
| X | sub02 | 2 | 300 | 1-apr |
| X | sub03 | 2 | 300 | 1-apr |
| Y | sub08 | 1 | 100 | 1-jan |
| Y | sub08 | 2 | 200 | 1-mrt |
| Y | sub08 | 3 | 500 | 1-apr |
| Z | sub03 | 1 | 200 | 1-feb |
| Z | sub01 | 2 | 50 | 1-apr |
The challenging part are the months without a new budget.
In those months we want to show the most recent budget, from previous months.
For example:
For Project X there is no budgets for the months february and march, so we want to
show the budget from january (sub01: 100 and sub02: 200).
Desired output:
| Project | Subtask | Budget | |
| 1-jan | X | sub01 | 100 |
| X | sub02 | 200 | |
| Y | sub08 | 100 | |
| 1-feb | X | sub01 | 100 |
| X | sub02 | 200 | |
| Y | sub08 | 100 | |
| Z | sub03 | 200 | |
| 1-mrt | X | sub01 | 100 |
| X | sub02 | 200 | |
| Y | sub08 | 200 | |
| Z | sub03 | 200 | |
| 1-apr | X | sub02 | 300 |
| X | sub03 | 300 | |
| Y | sub08 | 500 | |
| Z | sub01 | 50 |
Our current measure:
SUMX(
CALCULATETABLE(
SUMMARIZE(Budget_table,Budget_table[Project Name],"max_budget",sum('Budget_table'[Budget])),
FILTER(all('Date'),'Date'[Date]<=max('Date'[Date])),
FILTER(Budget_table,'Budget_table'[BudgetVersionNumber]= CALCULATE(max('Budget_table'[BudgetVersion]) ALLEXCEPT('Budget_table)',Budget_table[Project Name]),FILTER(all('Date'),'Date'[Date]<=max('Date'[Date])))),[max_budget])
2 Replies
- amitchandak
Super User
NickPowerBI , Try column like after join date with date of date table
Only repeat till year
YTD = CALCULATE(MAX(Table[Budget]),DATESYTD('Date'[Date],"12/31"))
repeat till end
Cumm = CALCULATE(MAX(Table[Budget]),filter(date,date[date] <=maxx(date,date[date])))Or
YTD = CALCULATE(lastnonblankvalue(Table[Date], MAX(Table[Budget]),DATESYTD('Date'[Date],"12/31"))
repeat till end
Cumm = CALCULATE(lastnonblankvalue(Table[Date], MAX(Table[Budget]),filter(date,date[date] <=maxx(date,date[date]))) - AnonymousNot applicable
// For the below to work you have to: // 1. Have a Dates table that's on the day granularity. // 2. It must be the Date table in the model. // 2. This table must be connected to the Budget Table. // 3. Even though the granularity of the [Budget Table] // is different than Dates, it must join on [Date] // and granularity must be managed through the code, // so you have to decide when to display the below. // The below is additive across proj/subtask but is // not additive over time. It only shows you the budget // for any particular date and any subset of proj/tasks. // If you want to make it somehow additive across time, // you'll have to either modify the measure or create // another one on top of this one. [Budget] = var __oneVersionVisible = HASONEFILTER( 'Budget Table'[Budget Version] ) var __latestDate = LASTDATE( Calendar[Date] ) var __result = if( __oneVersionVisible, var __latestDates = ADDCOLUMNS( // Get all visible proj's and tasks SUMMARIZE( 'Budget Table', 'Budget Table'[Project Name], 'Budget Table'[Subtask] ), // For each of the proj/tasks find // the last date before or on // the current one that does have // a value in the Budget Table. "@LatestDate", MAXX( FILTER( ALL( Dates ), Dates[Date] <= __latestDate && NOT( ISEMPTY( CALCULATETABLE( [Budget] ) )) ), Dates[Date] ) ) var __output = CALCULATE( SUM( 'Budget Table'[Budget] ), TREATAS( __latestDates, 'Budget Table'[Project Name], 'Budget Table'[Subtask], 'Dates'[Date] ) ) return __output ) RETURN __result