cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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.