Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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 @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
I would recommend you use hierarchies instead.
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.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |