Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have situation where I have to show previous months data in line graph based on month selection in slicer
My table
fiscal year | Month | Year | Value_1 | Value_2 | Value_3 | Value_4 |
2020 | July | 2019 | 15 | 24 | 54 | 32 |
2020 | August | 2019 | 5 | 35 | 65 | 24 |
2020 | September | 2019 | 65 | 69 | 87 | 98 |
2020 | October | 2019 | 7 | 87 | 96 | 78 |
2020 | November | 2019 | 256 | 25 | 24 | 58 |
2020 | December | 2019 | 359 | 654 | 63 | 54 |
2020 | January | 2020 | 2458 | 255 | 54 | 65 |
2020 | February | 2020 | 236 | 35 | 25 | 32 |
2020 | March | 2020 | 25 | 302 | 37 | 12 |
2020 | April | 2020 | 26 | 336 | 32 | 14 |
2020 | May | 2020 | 630 | 370 | 27 | 18 |
2020 | June | 2020 | 682 | 404 | 23 | 100 |
2021 | July | 2020 | 734 | 438 | 18 | 20 |
2021 | August | 2020 | 786 | 473 | 13 | 58 |
2021 | September | 2020 | 837 | 507 | 8 | 69 |
2021 | October | 2020 | 889 | 541 | 3 | 75 |
2021 | November | 2020 | 941 | 575 | 35 | 95 |
2021 | December | 2020 | 993 | 609 | 69 | 35 |
2021 | January | 2021 | 1045 | 643 | 87 | 15 |
2021 | February | 2021 | 1097 | 678 | 25 | 72 |
2021 | March | 2021 | 1148 | 712 | 654 | 82 |
2021 | April | 2021 | 1200 | 746 | 255 | 9 |
2021 | May | 2021 | 1252 | 780 | 35 | 5 |
2021 | June | 2021 | 1304 | 814 | 300 | 86 |
Now I have a slicer for Fiscal Year and Month
slicer
Fiscal Year I have 2020 and 2021(lets say we select 2020)
Month: July 2019 to June 2020(the months are in order July to June)
Now If I select July in the month Slicer then i have to show only July Month values
fiscal year | Month | Year | Value_1 | Value_2 | Value_3 | Value_4 |
2020 | July | 2019 | 15 | 24 | 54 | 32 |
Now If I select January in the month Slicer then i have to show July values to January
2020 | July | 2019 | 15 | 24 | 54 | 32 |
2020 | August | 2019 | 5 | 35 | 65 | 24 |
2020 | September | 2019 | 65 | 69 | 87 | 98 |
2020 | October | 2019 | 7 | 87 | 96 | 78 |
2020 | November | 2019 | 256 | 25 | 24 | 58 |
2020 | December | 2019 | 359 | 654 | 63 | 54 |
2020 | January | 2020 | 2458 | 255 | 54 | 65 |
Can anyone help me with this situation
Solved! Go to Solution.
Hi @Anonymous ,
Please follow the below steps to achieve it:
1. Create a fiscal year months table as below
2. Create a calculated column to get the fiscal month order
Nmonthorder = CALCULATE(MAX('Fiscal year'[Order]),FILTER('Fiscal year','Fiscal year'[Fiscal Month]='Table'[Month]))
3. Create 4 measures as below to get the the corresponding value_1, value_2, value_3 and value_4 in previous selected month
Nvalue_1 = var _sfMonth = SELECTEDVALUE('Fiscal year'[Fiscal Month])
var sfmonthOrder = CALCULATE(SUM('Fiscal year'[Order]),FILTER('Fiscal year','Fiscal year'[Fiscal Month] = _sfMonth))
return CALCULATE(SUM('Table'[Value_1]),FILTER('Table','Table'[Nmonthorder]<=sfmonthOrder))
Nvalue_2 = var _sfMonth = SELECTEDVALUE('Fiscal year'[Fiscal Month])
var sfmonthOrder = CALCULATE(SUM('Fiscal year'[Order]),FILTER('Fiscal year','Fiscal year'[Fiscal Month] = _sfMonth))
return CALCULATE(SUM('Table'[Value_2]),FILTER('Table','Table'[Nmonthorder]<=sfmonthOrder))
Nvalue_3 = var _sfMonth = SELECTEDVALUE('Fiscal year'[Fiscal Month])
var sfmonthOrder = CALCULATE(SUM('Fiscal year'[Order]),FILTER('Fiscal year','Fiscal year'[Fiscal Month] = _sfMonth))
return CALCULATE(SUM('Table'[Value_3]),FILTER('Table','Table'[Nmonthorder]<=sfmonthOrder))
Nvalue_4 = var _sfMonth = SELECTEDVALUE('Fiscal year'[Fiscal Month])
var sfmonthOrder = CALCULATE(SUM('Fiscal year'[Order]),FILTER('Fiscal year','Fiscal year'[Fiscal Month] = _sfMonth))
return CALCULATE(SUM('Table'[Value_4]),FILTER('Table','Table'[Nmonthorder]<=sfmonthOrder))
4. Drag the month field in new created fiscal year months table onto the slicer to replace the original month slicer, and put the new created 4 measures to replace the original values fields on table visual
I just create sample report file, you can get it from this link and check all of above information.
Best Regards
Rena
Hi @Anonymous ,
Please follow the below steps to achieve it as workaround:
1. Create a calculated column YearMonth
YearMonth =
VALUE (
CONCATENATE (
'Table'[Year],
SWITCH (
'Table'[Month],
"January", "01", "February", "02", "March", "03", "April", "04","May", "05","June", "06",
"July", "07","August", "08", "September", "09","October", "10","November", "11","December", "12"
)
)
)
2. Apply this calculated column on slicer to replace the original month slicer
Best Regards
Rena
but my requirement for this project is that i must show the months in the slicer
I cant replace the months with year month number
Hi @Anonymous ,
Please follow the below steps to achieve it:
1. Create a fiscal year months table as below
2. Create a calculated column to get the fiscal month order
Nmonthorder = CALCULATE(MAX('Fiscal year'[Order]),FILTER('Fiscal year','Fiscal year'[Fiscal Month]='Table'[Month]))
3. Create 4 measures as below to get the the corresponding value_1, value_2, value_3 and value_4 in previous selected month
Nvalue_1 = var _sfMonth = SELECTEDVALUE('Fiscal year'[Fiscal Month])
var sfmonthOrder = CALCULATE(SUM('Fiscal year'[Order]),FILTER('Fiscal year','Fiscal year'[Fiscal Month] = _sfMonth))
return CALCULATE(SUM('Table'[Value_1]),FILTER('Table','Table'[Nmonthorder]<=sfmonthOrder))
Nvalue_2 = var _sfMonth = SELECTEDVALUE('Fiscal year'[Fiscal Month])
var sfmonthOrder = CALCULATE(SUM('Fiscal year'[Order]),FILTER('Fiscal year','Fiscal year'[Fiscal Month] = _sfMonth))
return CALCULATE(SUM('Table'[Value_2]),FILTER('Table','Table'[Nmonthorder]<=sfmonthOrder))
Nvalue_3 = var _sfMonth = SELECTEDVALUE('Fiscal year'[Fiscal Month])
var sfmonthOrder = CALCULATE(SUM('Fiscal year'[Order]),FILTER('Fiscal year','Fiscal year'[Fiscal Month] = _sfMonth))
return CALCULATE(SUM('Table'[Value_3]),FILTER('Table','Table'[Nmonthorder]<=sfmonthOrder))
Nvalue_4 = var _sfMonth = SELECTEDVALUE('Fiscal year'[Fiscal Month])
var sfmonthOrder = CALCULATE(SUM('Fiscal year'[Order]),FILTER('Fiscal year','Fiscal year'[Fiscal Month] = _sfMonth))
return CALCULATE(SUM('Table'[Value_4]),FILTER('Table','Table'[Nmonthorder]<=sfmonthOrder))
4. Drag the month field in new created fiscal year months table onto the slicer to replace the original month slicer, and put the new created 4 measures to replace the original values fields on table visual
I just create sample report file, you can get it from this link and check all of above information.
Best Regards
Rena
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |