Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a date dimension and a fact table.
I have FiscalYear, FiscalQuarter, FiscalMonth in the date dimension and a measure in the fact table.
I want to create a column chart where the FiscalQuarters/FiscalMonth would be shown on the XAxis.
If I select Quarter2(this year) in the slicer , the XAxis should show Quarter2 & Quarter1 of this year and Quarter 4 of last year.
Solved! Go to Solution.
Hi @darshaningale ,
Firstly sorry for my method may be a bit difficult to understand and seems to weird😅 Let me explain to you:
According to your description, you want to show the previous 3 quarters in a visual, so my basic idea is to calculate the quarter-diff and if it in a special range, then set a flag=1.
The quarter-diff is a little complex as I thought before:
1 |
202104 | 202103 | 202102 | Diff=0,1,2 |
2 | 202103 | 202102 | 202101 | Diff=0,1,2 |
3 | 202102 | 202101 | 202004 | Diff=0,1,98 |
4 | 202101 | 202004 | 202003 | Diff=0,97,98 |
But now, thanks for @lbendlin suggestion, I realized we could add a Index column to limit the quarter-diff to between 0, 1, and 2 simply.
So please use RANKX() to add a Index column to ForSlicer table:
Index = RANKX('ForSlicer',[YearQuarter],,ASC,Dense)
Then change the Flag measure:
Flag =
VAR _max =
SELECTEDVALUE ( ForSlicer[YearQuarter] )
VAR _min =
CALCULATE (
MAX ( 'ForSlicer'[YearQuarter] ),
FILTER (
ALL ( 'ForSlicer' ),
'ForSlicer'[Index]
= SELECTEDVALUE ( ForSlicer[Index] ) - 2
)
)
VAR _yearQuarter =
MAX ( 'Date Dimension'[FiscalYear] ) * 100
+ MAX ( 'Date Dimension'[FiscalQuarter] )
RETURN
IF ( _yearQuarter <= _max && _yearQuarter >= _min, 1, 0 )
In addition, as you mentioned: This is somewhat different to my data and format. please provide me with more details about your table or share me with your pbix file after removing sensitive data.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @darshaningale ,
Since The requirement has changed, I suggest you create a new post for further help.
And If my second post makes sense to you , please consider Accept it as the solution to help the other members find it more quickly.😀 Thanks in advance.
Best Regards,
Eyelyn Qin
Thank you for your support. Your solution works for previous quarters and forecast quarters also if the logic is changed accordingly.
Hi @darshaningale ,
Could you tell me if my second post helps you a little? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.Hope to hear from you😀
Best Regards,
Eyelyn Qin
Hi Evelyn,
thank you for that detailed explanation.
The requirement has changed. i have to show the forecasted periods(not quarters) now and i am trying to understand how can i do that with your solution..
The solution looks promising but i am yet to implement it.
P.S. : i cannot send the pbix file as the data model is too complicated and too time consuming to remove the sensitive data.
Hi,
See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
Hi @darshaningale ,
Firstly sorry for my method may be a bit difficult to understand and seems to weird😅 Let me explain to you:
According to your description, you want to show the previous 3 quarters in a visual, so my basic idea is to calculate the quarter-diff and if it in a special range, then set a flag=1.
The quarter-diff is a little complex as I thought before:
1 |
202104 | 202103 | 202102 | Diff=0,1,2 |
2 | 202103 | 202102 | 202101 | Diff=0,1,2 |
3 | 202102 | 202101 | 202004 | Diff=0,1,98 |
4 | 202101 | 202004 | 202003 | Diff=0,97,98 |
But now, thanks for @lbendlin suggestion, I realized we could add a Index column to limit the quarter-diff to between 0, 1, and 2 simply.
So please use RANKX() to add a Index column to ForSlicer table:
Index = RANKX('ForSlicer',[YearQuarter],,ASC,Dense)
Then change the Flag measure:
Flag =
VAR _max =
SELECTEDVALUE ( ForSlicer[YearQuarter] )
VAR _min =
CALCULATE (
MAX ( 'ForSlicer'[YearQuarter] ),
FILTER (
ALL ( 'ForSlicer' ),
'ForSlicer'[Index]
= SELECTEDVALUE ( ForSlicer[Index] ) - 2
)
)
VAR _yearQuarter =
MAX ( 'Date Dimension'[FiscalYear] ) * 100
+ MAX ( 'Date Dimension'[FiscalQuarter] )
RETURN
IF ( _yearQuarter <= _max && _yearQuarter >= _min, 1, 0 )
In addition, as you mentioned: This is somewhat different to my data and format. please provide me with more details about your table or share me with your pbix file after removing sensitive data.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @darshaningale ,
You could kindly follow @lbendlin 's suggestion.
Here is my example you could refer to:
1.Create a new table for slicer:
ForSlicer = DISTINCT( SELECTCOLUMNS('Date Dimension',"YearQuarter",[FiscalYear]*100+[FiscalQuarter]))
2.Create a measure for filter:
Flag =
VAR _sele =
SELECTEDVALUE ( ForSlicer[YearQuarter] )
VAR _yearQuarter =
MAX ( 'Date Dimension'[FiscalYear] ) * 100
+ MAX ( 'Date Dimension'[FiscalQuarter] )
RETURN
IF ( _sele - _yearQuarter IN { 0, 1, 2, 97, 98 }, 1, 0 )
3. Apply the filter to visual-level filter, set as "is 1". The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
How can i do this for Periods and not Quarters ? Because Period is my lowest level of granularity.
what do you mean by _sele - _yearQuarter IN { 0, 1, 2, 97, 98 }
This is caused by the fancy date math that @v-eqin-msft is doing.
[FiscalYear]*100+[FiscalQuarter]
then she checks if the difference between the selected yearquarter and the row yearquarter is in the list of values. If you truly want the "previous three quarters" only then you should use an index column for that yearquarter and the formula should be
_sele - _yearQuarter IN { 1, 2, 3 }
This is somewhat different to my data and format. But i will try this and let you know.
Your slicer will need to be fed by a separate, disconnected table (containing only the YearQuarter strings, for example). Then you can adjust your measure to calculate your fact data accordingly.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
86 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |