How to show data from previous 3 months to next 15 months from year-month slicer?

[TusharGaurav]

Hi Experts,

I have  Year and Month slicers in my report and I am showing a table visual with Year ,month and  revenue.

Now if I select 2023 as year and March as month, then in the table visual, I should have Revenue from 2023-Jan to 2024-June.

Can you please suggest how to get this scenario.

Thanks and Regards,

Tushar Gaurav

[v-pgoloju]

Hi  @TusharGaurav 

Just a gentle reminder  has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @TusharGaurav,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @TusharGaurav,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[HarishKM]

@TusharGaurav Hey,
You can try below measure it seems to working as expected

Measure 1:

Revenue =
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR SelectedMonth = SELECTEDVALUE('DateTable'[Month])

VAR StartDate = DATE(SelectedYear, 1, 1)
VAR EndDate = DATE(SelectedYear + 1, 6, 30)

RETURN
CALCULATE(
SUM('RevenueTable'[Revenue]),
'DateTable'[Date] >= StartDate && 'DateTable'[Date] <= EndDate
)

Try above measure

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[TusharGaurav · Accepted solution]

Hi Harish,

Thanks for your help.However, the measure is also not working.

I am still not able to show the data monthwise inplace of aggregated revenue for the month/year selected.

[v-pgoloju]

Hi @TusharGaurav,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Please try the fallowing dax to create Measure.

ShowRevenue =
VAR SelectedYear = SELECTEDVALUE('DateSlicer'[Year])
VAR SelectedMonth = SELECTEDVALUE('DateSlicer'[MonthNumber])
VAR SelectedDate = DATE(SelectedYear, SelectedMonth, 1)

VAR StartDate = EOMONTH(SelectedDate, -3)+1
VAR EndDate = EOMONTH(SelectedDate, 15)

RETURN
CALCULATE(
SUM('Sales'[Revenue]),
FILTER(
ALL('DateSlicer'),
'DateSlicer'[Date] >= StartDate &&
'DateSlicer'[Date] <= EndDate
)
)

I’ve attempted to recreate the scenario using sample data.

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & regards,
Prasanna Kumar

[TusharGaurav]

Hi Prasanna,

Thanks for your help.

Is there a way there I can show the data monthwise inplace of aggregated revenue for the month/year selected.

Thanks and Regards,

Tushar Gaurav

[v-pgoloju]

Hi @TusharGaurav,

Try using modified dax to get the requirement.

ShowRevenueMonthWise :=
VAR SelectedYear = SELECTEDVALUE('DateSlicer'[Year])
VAR SelectedMonth = SELECTEDVALUE('DateSlicer'[MonthNumber])
VAR SelectedDate = DATE(SelectedYear, SelectedMonth, 1)

VAR StartDate = EOMONTH(SelectedDate, -3) + 1
VAR EndDate = EOMONTH(SelectedDate, 15)

RETURN
CALCULATE(
SUM('Sales'[Revenue]),
'DateSlicer'[Date] >= StartDate &&
'DateSlicer'[Date] <= EndDate
)

If you found this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to help others in the community.

Thank you & regards,
Prasanna Kumar

[TusharGaurav]

Hi Pankaj,

Thanks for your response but the dax calculation is not working for me and I was getting error in the same.

I have modified your calculation little bit as below:

ShowMonth =
VAR _SelectedDate =
DATE(
SELECTEDVALUE('Calendar Lookup'[Year]),
SELECTEDVALUE('Calendar Lookup'[Month]),
1)
VAR _MinDate = EOMONTH(_SelectedDate, -3) + 1 -- Start from first day after 3 months before
VAR _MaxDate = EOMONTH(_SelectedDate, 15) -- End of 15 months ahead
VAR _CurrentDate = DATE(max('Calendar Lookup'[Year]), max('Calendar Lookup'[Month]), 1) --change made
RETURN
IF(
_CurrentDate >= _MinDate && _CurrentDate <= _MaxDate,
1,
0
)

Was it working for you. If yes then can you plz share the pbix file.

Thanks, 

Tushar

[pankajnamekar25]

You can use this measure as visual level filter

Assuming that you have calender table

ShowMonth =

VAR _SelectedDate =

    DATE(

        SELECTEDVALUE('Calendar'[Year]),

        SELECTEDVALUE('Calendar'[Month]),

        1

    )

VAR _MinDate = EOMONTH(_SelectedDate, -3) + 1 -- Start from first day after 3 months before

VAR _MaxDate = EOMONTH(_SelectedDate, 15) -- End of 15 months ahead

VAR _CurrentDate = DATE('Calendar'[Year], 'Calendar'[Month], 1)

RETURN

IF(

    _CurrentDate >= _MinDate && _CurrentDate <= _MaxDate,

    1,

    0

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.