Forum Discussion
Dynamic Quarter Year Calculation
Hi , I need help creating a list view which is coming from two table , one store the data at deal level and one store the data Year and quarter leval . I wanted to show all deal level data along with each deal Year and Qaurter value . So this Year and Quarter should be display as column but they should be also populated dynmically so , if we select 2023 Q1 data , in the list it should show deal level data and 2023Q1 ,2023Q2,2023Q3,2023Q4.
Deal level Data:
Year and QTR Data :
Data Should show if we selected Q1FY20 in Slicer
where when we pulling the data from YearQuarter table its also summarizing the value at deal level.
4 Replies
Hi PowerBI_User23 ,
I have some doubts about your requirement, could you please help clear it? Thank you.
- What's your raw data looks like? Could you please provide some sample data? You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
- Is there any date field in the fact table?
- How to get the value of field [QxFY20 ] in the visual? What's the calculation logic?
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi Anonymous Thanks for your reply.
There are two table involved one Deal Level Data and another one is Year and Quarter Data and I have also put the desired output :
1-Deal Level Data :Case UID
Location
Date
Amount
301
India
Jan 1, 2022
100.00
302
US
Mar 1, 2022
101.00
303
Japan
Mar 1, 2022
102.00
304
Canada
Apr 1, 2022
103.00
305
Italy
Jan 1, 2022
104.00
306
Spain
Jan 1, 2022
105.00
307
Switzerland
May 1, 2022
106.00
308
Colombia
Jun 1, 2022
107.00
309
US
Feb 1, 2022
108.00
Second table Store amount Year and Quarter Wise :
2-Year and Quarter DataCase UID
Year Num
QTR NM
Amount
301
2022
1
10
301
2022
2
20
301
2022
3
10
301
2022
4
30
301
2023
1
10
301
2023
2
10
301
2023
3
5
301
2023
4
5
302
2022
1
40
302
2022
2
30
302
2022
3
20
302
2022
4
10
302
2023
1
1
303
2022
2
10
303
2022
3
20
303
2022
4
10
303
2023
1
30
303
2023
2
10
303
2023
3
10
303
2023
4
5
303
2024
1
7
304
2022
2
10
304
2022
3
50
304
2022
4
20
304
2023
1
23
306
2022
1
10
306
2022
2
5
306
2022
3
5
306
2022
4
40
306
2023
1
30
306
2023
2
10
306
2023
3
5
And this is the Desired Output which come after joining these two tables and if we have selected FY 2022 Q2 then output should come like this:Case UID
Location
Date
Amount
FY 2022 Q2
FY 2022 Q3
FY 2022 Q4
FY 2023 Q1
301
India
Jan 1, 2022
100.00
20
10
30
10
302
US
Mar 1, 2022
101.00
30
20
10
1
303
Japan
Mar 1, 2022
102.00
10
20
10
30
304
Canada
Apr 1, 2022
103.00
10
50
20
23
305
Italy
Jan 1, 2022
104.00
0
0
0
0
306
Spain
Jan 1, 2022
105.00
5
5
40
30
307
Switzerland
May 1, 2022
106.00
0
0
0
0
308
Colombia
Jun 1, 2022
107.00
0
0
0
0
309
US
Feb 1, 2022
108.00
0
0
0
0
And to answer all your point , here is my input :
- What's your raw data looks like? Could you please provide some sample data? You can refer the following links to share the required info: Raw data I have shared in pbix file also , there are two table involved as I have showed to you in above table point 1 and 2.
- Is there any date field in the fact table? Yes there is an amount field in deal level data table and all the YR and Qtr data are fact data.
- How to get the value of field [QxFY20 ] in the visual? What's the calculation logic? It comes from table Year and Quarter Data and store in same format .
link for pbix file :https://drive.google.com/file/d/1sdqML7t07Q-StBB3SAM7uaj_k3mYaqTh/view?usp=sharing
Hi PowerBI_User23 ,
I updated your sample pbix file(see 'Page 2' in the attachment), please check if that is what you want.
1. Create a year quarter dimension table
2. Create a measure as below
Measure = VAR _yquarter = SELECTEDVALUE ( 'YearQuarter'[YearQuarter] ) VAR _qtr = VALUE ( RIGHT ( _yquarter, 1 ) ) VAR _year = VALUE ( MID ( _yquarter, 3, 4 ) ) VAR _yq = VALUE ( _year & "0" & _qtr ) RETURN CALCULATE ( SUM ( 'Qtrly Year Amount'[Amount] ), FILTER ( 'Qtrly Year Amount', VALUE ( 'Qtrly Year Amount'[Year Num] & "0" & 'Qtrly Year Amount'[QTR NM] ) >= _yq && VALUE ( 'Qtrly Year Amount'[Year Num] & "0" & 'Qtrly Year Amount'[QTR NM] ) <= IF ( _qtr = 1, VALUE ( _year & "04" ), VALUE ( ( _year + 1 ) & "0" & ( _qtr - 1 ) ) ) ) )3. Create a slicer using the field [YearQuarter] of dimension table 'YearQuarter'
4. Create a matrix visual as below screenshot
Best Regards
HI Anonymous Thankyou for your solution it was so quick , the only issue is here , i dont want to create this view using matrix , because when we extract this data into excel it will give output something like this(shown in last image) , is it any way we can pull this data in list or table view using this option :
Because after extracting the data into excel its giving data something like this .
we dont want this view in excel , we want same as we are showing in html format ,
can we create some dynmic clolumn of these qtrs as column and then we can join with deal level data to show in list /Table , or do you have any other good solution for this
