topic Re: Dynamically adding columns in DAX Commands and Tips

Dynamically adding columns

Jithin00 — Sat, 06 Jul 2024 07:57:40 GMT

Hey all, i have got 4 quaters that is q1=oct,nov,dec q2=Jan,feb,mar q3=april,may,june q4=july,aug,sept

(Note:- When the quater ends it should print the last 2 months data of the quater dynamically.)

So currently q1,q2,q3 is over so the table looks like this

	q1	q2	q3	may	june
Innovations	5	5	5	5	5
Loyalty	4	4	4	4	4
Value	6	6	6	6	6

But when quater 4 ends it should dynamically create columns q4, aug, sept and should look like

	q1	q2	q3	q4	aug	sept
Innovations	5	5	5	5	5	5
Loyaltly	4	4	4	4	4	4
Value	6	6	6	6	6	6

Requesting for help for the above problem.

Thankyou

Re: Dynamically adding columns

foodd — Sat, 06 Jul 2024 17:19:13 GMT

Hello Jithin00 , remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Re: Dynamically adding columns

lbendlin — Sat, 06 Jul 2024 19:02:37 GMT

I would recommend you use hierarchies instead.

Re: Dynamically adding columns

Anonymous — Tue, 23 Jul 2024 02:48:15 GMT

lbendlin foodd Thanks for your contribution on this thread.

Hi Jithin00 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a calculated column as below in DateTable

QtrOrMonth = VAR _today = TODAY () VAR _year = YEAR ( _today ) VAR _month = MONTH ( _today ) VAR _date = 'DateTable'[Date] VAR _qtr = CALCULATE ( MAX ( 'DateTable'[Quarter] ), FILTER ( 'DateTable', 'DateTable'[Date] = _today ) ) VAR _qbdate = IF ( _qtr = "Q1", DATE ( _year, 10, 1 ), DATE ( _year - 1, 10, 1 ) ) VAR _qedate = SWITCH ( _qtr, "Q2", DATE ( _year - 1, 12, 31 ), "Q3", DATE ( _year, 3, 31 ), "Q4", DATE ( _year, 6, 30 ) ) VAR _month1 = EOMONTH ( _qedate, -2 ) + 1 RETURN IF ( _date >= _qbdate && _date <= _qedate, IF ( _date >= _month1 && _date <= _qedate, [MonthName], [Quarter] ) )

2. Create a sort order table

3. Create the following measures

Sum of Innovations = VAR _qmonth = SELECTEDVALUE ( 'Sort Order'[QtrMonth] ) VAR _qvalue1 = CALCULATE ( SUM ( 'Table'[Innovations] ), FILTER ( ALLSELECTED ( 'DateTable' ), 'DateTable'[QtrOrMonth] = _qmonth ) ) VAR _qvalue2 = CALCULATE ( SUM ( 'Table'[Innovations] ), FILTER ( ALLSELECTED ( 'DateTable' ), 'DateTable'[Quarter] = _qmonth ) ) RETURN IF ( LEFT ( _qmonth, 1 ) = "Q", IF ( _qvalue1 < _qvalue2, _qvalue2, _qvalue1 ), SUM ( 'Table'[Innovations] ) )

Sum of Loyalty = VAR _qmonth = SELECTEDVALUE ( 'Sort Order'[QtrMonth] ) VAR _qvalue1 = CALCULATE ( SUM ( 'Table'[Loyalty] ), FILTER ( ALLSELECTED ( 'DateTable' ), 'DateTable'[QtrOrMonth] = _qmonth ) ) VAR _qvalue2 = CALCULATE ( SUM ( 'Table'[Loyalty] ), FILTER ( ALLSELECTED ( 'DateTable' ), 'DateTable'[Quarter] = _qmonth ) ) RETURN IF ( LEFT ( _qmonth, 1 ) = "Q", IF ( _qvalue1 < _qvalue2, _qvalue2, _qvalue1 ), SUM ( 'Table'[Loyalty] ) )

Sum of Value = VAR _qmonth = SELECTEDVALUE ( 'Sort Order'[QtrMonth] ) VAR _qvalue1 = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'DateTable' ), 'DateTable'[QtrOrMonth] = _qmonth ) ) VAR _qvalue2 = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'DateTable' ), 'DateTable'[Quarter] = _qmonth ) ) RETURN IF ( LEFT ( _qmonth, 1 ) = "Q", IF ( _qvalue1 < _qvalue2, _qvalue2, _qvalue1 ), SUM ( 'Table'[Value] ) )

4. Create a matrix visual(Columns: [QtryMonth] field of 'Sort Order' table Values: the above 3 measures)

Best Regards