Dynamically adding columns

[Jithin00]

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

[Anonymous · Accepted solution]

@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

[lbendlin]

I would recommend you use hierarchies instead.

[foodd]

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-...

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/1447...

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