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

Helper II

## Based on date slicer- display current month and previous month names

Hi All,

I have calender[date] column which is being used in slicer hierarchy level based on selection it should display current month& year, previous month& year  in card label.

example screenshot :

if user selected in slicer  Jan'2023 then

current month_year = Jan 2023

pre month_Year = Dec 2022

1 ACCEPTED SOLUTION
Super User

Hi @MSuser5 ,

1-Create a calculated column as:

YearMonth = CONVERT(FORMAT(DateTable[Date],"YYYYMM"),INTEGER)

2-Create another calculated column as:
YearMonthOrder = RANKX (
DateTable,
DateTable[YearMonth],
,
ASC,
DENSE
)

3-CurrentMonth Measure is as below:
CurrentMonth =
CALCULATE (
MIN ( DateTable[MonthName] ),
FILTER (
ALL ( DateTable ),
DateTable[YearMonthOrder] = MAX ( DateTable[YearMonthOrder] )
)
)

4-PrevMonth Measure is:
PrevMonth =
CALCULATE (
MIN ( DateTable[MonthName] ),
FILTER (
ALL ( DateTable ),
DateTable[YearMonthOrder]
MAX ( DateTable[YearMonthOrder] ) - 1
)
)

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.
Regards,
Loran
2 REPLIES 2
Super User

Hi @MSuser5 ,

1-Create a calculated column as:

YearMonth = CONVERT(FORMAT(DateTable[Date],"YYYYMM"),INTEGER)

2-Create another calculated column as:
YearMonthOrder = RANKX (
DateTable,
DateTable[YearMonth],
,
ASC,
DENSE
)

3-CurrentMonth Measure is as below:
CurrentMonth =
CALCULATE (
MIN ( DateTable[MonthName] ),
FILTER (
ALL ( DateTable ),
DateTable[YearMonthOrder] = MAX ( DateTable[YearMonthOrder] )
)
)

4-PrevMonth Measure is:
PrevMonth =
CALCULATE (
MIN ( DateTable[MonthName] ),
FILTER (
ALL ( DateTable ),
DateTable[YearMonthOrder]
MAX ( DateTable[YearMonthOrder] ) - 1
)
)

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.
Regards,
Loran
Helper II

Thank you so much it's working as expected.