Forum Discussion

NickPowerBI's avatar
NickPowerBI
Regular Visitor
6 years ago

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 NameSubtaskBudget VersionBudgetDate
Xsub0111001-jan
Xsub0212001-jan
Xsub0223001-apr
Xsub0323001-apr
Ysub0811001-jan
Ysub0822001-mrt
Ysub0835001-apr
Zsub0312001-feb
Zsub012501-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:

 

 ProjectSubtaskBudget
1-janXsub01100
 Xsub02200
 Ysub08100
1-febXsub01100
 Xsub02200
 Ysub08100
 Zsub03200
1-mrtXsub01100
 Xsub02200
 Ysub08200
 Zsub03200
1-aprXsub02300
 Xsub03300
 Ysub08500
 Zsub0150


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

  • 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])))

  • Anonymous's avatar
    Anonymous
    Not 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