Selected Value Nested In Calculate

[asteinbach]

Hello,

I'm trying to calculate a YTD number. Essentially, a dashboard will be refreshed monthly that has financial data by month. I have a slicer for months (January through December), but I want the user to be able to select the month and be able to see the "year to date" based on that specific month. Below is an extremely basic example of what the results would be. 

Month	Monthly Value	YTD
January	100	100
February	200	300
March	250	550

I have two tables to calculate this in my file: Revenue_COGS (where the financial data is) and Months (essentiall just a mapping thing).

Revenue_COGS has headers Year, Month, Account, Scenario, Value, and Month Number

Months has headers Month, Number, and Date_Format (just the month transformed into date format for hiearchy purposes).

I essentially need a way to get selected value (or an equivilent) to work within the calculate function. I want the user to select "March" on the slicer, and have a matrix return 250 for month and 550 for YTD. My calculation is currently written as the following:

Revenue_Calc_2021_Actual = Calculate(sum(Revenue_COGS[Value]),'Revenue_COGS'[Account] = "Revenue",'Revenue_COGS'[Year]=2021,'Revenue_COGS'[Scenario] = "ACTUAL",selectedvalue(Months[Date_Format].[MonthNo])>=Revenue_COGS[Month Number])

This doesn't work however because selectedvalue isn't permitted inside calculate. I've tried nesting the filter function within the calulate function as well, and it doesn't work. The second I select a month on the slicer it still filters everything to just that month. Just to test it, I changed the formula to this:

Revenue_Calc_2021_Actual = Calculate(sum(Revenue_COGS[Value]),'Revenue_COGS'[Account] = "Revenue",'Revenue_COGS'[Year]=2021,'Revenue_COGS'[Scenario] = "ACTUAL",Revenue_COGS[Month Number]<=3)

and it netted the exact result. However, this isn't dynamic and a user wouldn't be able to change this. Is there a way to make this dynamic, where a month can be selected and it will sum all "month numbers" equal to and prior?

Thank you in advance. 

[Anonymous]

HI @asteinbach,

Did you use the function in a calculated column? AFAIK, current power bi does not support creating a calculated column/table that interacts with filter/slicers. They are host on different data levels and not able to use child level to affect its parent.

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng