cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Last N months from selected month

Hi Team,

Please do needful for the following criteria.
I have the below kind of data, the requirement i need is , if we select May, we need to show last four months data(Feb, Mar, apr, May) . and it should be based on year also(Ex:- if we select 2022 feb, then result is 2021 Nov, 2021 dec, 2022 jan, 2022 feb)

1 ACCEPTED SOLUTION
Community Support

Hi @siva54 ,

You may try this solution.

1 Create a date column with the following Calculated column in your sample table

``````date =
VAR mon_ =
SWITCH (
'Table'[Month],
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
RETURN
DATE ( 'Table'[Year], mon_, 1 )``````

Then, the sample table will look like this.

2 Using the date column from calendar table to create Slicers

3 Create a measure as follows and take it to create your visual

``````LastFourMonth =
VAR selectedYear =
SELECTEDVALUE ( 'calendar'[Date].[Year] )
VAR selectedMon =
SELECTEDVALUE ( 'calendar'[Date].[MonthNo] )
VAR start_ =
DATE ( selectedYear, selectedMon - 4, 1 )
VAR end_ =
DATE ( selectedYear, selectedMon + 1, 1 ) - 1
RETURN
CALCULATE (
SUM ( 'Table'[Values] ),
FILTER ( 'Table', 'Table'[date] > start_ && 'Table'[date] <= end_ )
)``````

The visuals will look like this.

Also, attach the pbix for reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

4 REPLIES 4
Community Support

Hi @siva54 ,

You may try this solution.

1 Create a date column with the following Calculated column in your sample table

``````date =
VAR mon_ =
SWITCH (
'Table'[Month],
"January", 1,
"February", 2,
"March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
"December", 12
)
RETURN
DATE ( 'Table'[Year], mon_, 1 )``````

Then, the sample table will look like this.

2 Using the date column from calendar table to create Slicers

3 Create a measure as follows and take it to create your visual

``````LastFourMonth =
VAR selectedYear =
SELECTEDVALUE ( 'calendar'[Date].[Year] )
VAR selectedMon =
SELECTEDVALUE ( 'calendar'[Date].[MonthNo] )
VAR start_ =
DATE ( selectedYear, selectedMon - 4, 1 )
VAR end_ =
DATE ( selectedYear, selectedMon + 1, 1 ) - 1
RETURN
CALCULATE (
SUM ( 'Table'[Values] ),
FILTER ( 'Table', 'Table'[date] > start_ && 'Table'[date] <= end_ )
)``````

The visuals will look like this.

Also, attach the pbix for reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

Solution Sage

Hi:

Here are a couple of ways to do this. I think page two is your initial request. The first page is doing a running total on the number of months selected. Page two is Relative so if you set it to 6, it will always show last six months.

Helper I

the view will be like this.

Here we selected 2022 feb, so need to show last 5 months data.

Solution Sage

Hello:

I added a date table for future time intel calculations to be easier. I have used made up figures. I hope this helps!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors