Dynamically Creating a column for year based on value

[Joseph_Hchaime]

Hi I have a scenario where I have outstanding balances on bills. I need to group these outstanding balances by the YEAR the bill was issued in. My problem is I need to show that in a table visual dynamically instead of creating individual columns i.e. 2019 = CALCULATE(SUM(Outstanding),Filter(YEAR(Bill_issue_Date)=2019) (repeating that for all years). 

Is there a way i can do that dynamically for all years available in the dataset?

[Greg_Deckler · Accepted solution]

@Joseph_Hchaime Would be helpful to have sample data and expected output. However, in many cases you can't use a measure in the way you want like in an axis, etc. Generally in those cases you create a disconnected calculated table and write your measure accordingly. So you could, for example, create a calculated table that grabs the min and max dates and then uses GENERATESERIES to create the years, like:

Table = 
  VAR __Max = MAX('Dates'[Date])
  VAR __Min = MIN('Dates'[Date])
  VAR __Result = GENERATESERIES(__Min, __Max, 1)
RETURN
  __Result

Here is an article on disconnected tables and measures: In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...