Forum Discussion
Dynamically adding columns
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
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
3 Replies
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
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.I would recommend you use hierarchies instead.
