Forum Discussion
Multiplication with a variable
Hello everyone,
I have troubles building a measure in Power BI.
I calculated monthly expenditures on Power BI (I divided the whole amount per 12).
I want to have the year-to-date expenditures, calculated automatically when you choose the month.
The formula looks like that :
Year-to-date expenditures = [Monthly expenditures] * [Number of the month]
For example, in March, you multiple by 3.
I tried with VAR but it doesn't work.
This is what I wrote.
Ytd Expenditures =
VAR
SelectedMonth = IF(HASONEVALUE('Date 2'[Month Number]),FIRSTNONBLANK('Date 2'[Month Number],'Date 2'[Month Number]),BLANK() )-1
Return
CALCULATE([Monthly expenditures Cosmetics]*SelectedMonth,ALL('Date 2'),'Date 2'[Month Number]=SelectedMonth)
Thank you for your help.
Anonymous
Try like
Ytd Expenditures = VAR SelectedMonth = countX('Date 2', Values('Date 2'[Month Number])) Return CALCULATE([Monthly expenditures Cosmetics]*SelectedMonth) Ytd Expenditures = VAR SelectedMonth = MaxX('Date 2'('Date 2'[Month Number])) Return CALCULATE([Monthly expenditures Cosmetics]*SelectedMonth)
2 Replies
Anonymous
Try like
Ytd Expenditures = VAR SelectedMonth = countX('Date 2', Values('Date 2'[Month Number])) Return CALCULATE([Monthly expenditures Cosmetics]*SelectedMonth) Ytd Expenditures = VAR SelectedMonth = MaxX('Date 2'('Date 2'[Month Number])) Return CALCULATE([Monthly expenditures Cosmetics]*SelectedMonth)