Show previous months values for a selected month(I have months and Years in a specific order)

[Anonymous]

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

[v-yiruan-msft]

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

[Anonymous]

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

[v-yiruan-msft · Accepted 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