Forum Discussion
Dynamically Creating a column for year based on value
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?
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 __ResultHere 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/ba-p/279563
1 Reply
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 __ResultHere 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/ba-p/279563