cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to Show Previous Months Values for a selected month and fiscal year

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

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

Based on your sample data, I add a MonthNum column in it  and create a calculated columnFYMonthNum ( please refer to https://www.powerobjects.com/blog/2018/12/19/sorting-data-fiscal-year-power-bi/)for sort data in slicer:

``FYMonthNum = VAR FYStartMonth = 7 RETURN IF ('Table'[MonthNum] >= FYStartMonth,'Table'[MonthNum] -( FYStartMonth-1), 12+('Table'[MonthNum] -(FYStartMonth-1)))``

Create a calculated table for slicer (Month) :

``Table 2 = SUMMARIZE('Table','Table'[Month],'Table'[FYMonthNum])``

Sort the Month column by FYMonthNum column:

Create a measure and add it to visual level filter:

``measure = IF(MAX('Table'[FYMonthNum]) <= CALCULATE(VALUES('Table'[FYMonthNum]),FILTER(ALL('Table'),'Table'[Month] = MAX('Table 2'[Month]))),1,0)``

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Dedmon Dai

Community Support

Hi @Anonymous ,

Based on your sample data, I add a MonthNum column in it  and create a calculated columnFYMonthNum ( please refer to https://www.powerobjects.com/blog/2018/12/19/sorting-data-fiscal-year-power-bi/)for sort data in slicer:

``FYMonthNum = VAR FYStartMonth = 7 RETURN IF ('Table'[MonthNum] >= FYStartMonth,'Table'[MonthNum] -( FYStartMonth-1), 12+('Table'[MonthNum] -(FYStartMonth-1)))``

Create a calculated table for slicer (Month) :

``Table 2 = SUMMARIZE('Table','Table'[Month],'Table'[FYMonthNum])``

Sort the Month column by FYMonthNum column:

Create a measure and add it to visual level filter:

``measure = IF(MAX('Table'[FYMonthNum]) <= CALCULATE(VALUES('Table'[FYMonthNum]),FILTER(ALL('Table'),'Table'[Month] = MAX('Table 2'[Month]))),1,0)``

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Dedmon Dai

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors