PowerBi Desktop YTD Filter

[MRstwb]

Hi everyone,

I am wondering if there is a way to output YTD values ​​as a sum of each month?

Via an interface to Salesforce I am able to import contracts based on a key date.
Logically, the contract budgets only appear once in the first month.

I would like to have an overview of the current (fixed) budgets (as of today) and sales opportunities, but as a YTD calculation. For example, if a budget of €20,000 is won in June, this is only included once in the YTD calculation.

Example:

January current budget: 10.000€
January sales opportunity: €5,000
February current budget: €20,000
February sales opportunity: 0€
March current budget: 10.000€
March sales opportunity: €5,000

Total current budget for the first quarter: €40,000.
Total sales opportunities for the first quarter: €10,000.

However, since the budget gained from January can be invoiced monthly, it must also be taken into account in February and March. --> According to PowerBi, the YTD total would be €50,000. In reality, however, this is €60,000 (since the sales opportunity from January in February and March must also be taken into account).

Is there a fix for this?

[amitchandak]

@MRstwb , YTD sales

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

YTD budget

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE(calculate([budget], dateadd('Date'[Date],1,Month)) ,DATESBETWEEN('Date'[Date],_min,_max))

assume net/sales and budget is measure

[MRstwb]

Hi amitchandak,

thank's for your quick respone.

But it seems that this gets me the same output and Powerbi does not aggregate the previous months.

The YTD value shows me only 10.000€, but it should be in total 20.000€

January: 5.000€

February: 5.000€ from January

March: 5.000€ from January plus 5.000€ from March.

	new acquired Opportunities	Opportunities YTD	PowerBi YTD
January	5.000,00 €	5.000,00 €	5.000,00 €
February	-   €	5.000,00 €	5.000,00 €
March	5.000,00 €	20.000,00 €	10.000,00 €

Don't know if YTD is the right measure. I think it should be a calculating sum for each month which works as a loop.

Thank you in advance.

Best

MRstwb

[v-rzhou-msft · Accepted solution]

Hi @MRstwb ,

As far as I know, Dax doesn't support us to calculate the loop. Here I suggest you to try Power Query to calculate the loop.

Here are some offical blogs and solved post, I hope they could help you solve your problem.

For reference:

Power Query - Loop calculating

Power Query Loop Calculation

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.