DAX Workout 005 - Create a dynamic slicer

[tweinzapfel]

Everyone,

This workout is about creating a dynamic slicer. In the attached file you are to create a simple bar chart as shown in the example. Then, add in a drop down slicer showing the month and year. However, for the slicer, the first entry should be listed as “Current Month (Apr 2023)” (or the current month when report is loaded). The second entry should show as “Previous Month (Mar 2023)” and then all remaining months as shown in the example.

(Note - Mar 2023 is showing up as the “current month” as this workout was created during this time)

Additional criteria is included in the picture below.

The PBI file located here:
Workout 005_Posting.pbix (1.3 MB)

Submission
Load the supplied file, create your solution, and reply to this post. Upload a screenshot of your solution (or the PBI file) along with any DAX items. Please format your DAX code and blur it or place it in a hidden section.

[KimC]

Hi Tim,
Thanks for another great workout which I enjoyed.

My solution:

Summary

Instead of dax I used Power Query and created a calculated column.

My mcode:

DAX Workout 005_Dynamic Mth Slicer.pbix (1.3 MB)

[hassanashaskhan]

Here’s my solution to the workout problem.

I created two Calculated Columns within the Dates Table,

Months Difference from Today = 

    var _MonthsDifference = DATEDIFF(Dates[Date], TODAY(), MONTH)

    var _Result = 
        SWITCH(
            TRUE(), 
            _MonthsDifference = 0, "Current Month (" & Dates[Month & Year] & ")", 
            _MonthsDifference = 1, "Previous Month (" & Dates[Month & Year] & ")", 
            Dates[Month & Year]
        )

    return _Result

I created another column to sort the first column and also to help in removing the future months from the current month.

Month Difference From Today (For Sorting) = 

    var _MonthsDifference = DATEDIFF(Dates[Date], TODAY(), MONTH)

    var _Sorting_Order = 
        SWITCH(
            TRUE(), 
            _MonthsDifference < 0, 9999, 
            _MonthsDifference
        )

    return _Sorting_Order

Other than that, I created a Measure for the Subtitle of my Bar Graph

Selected Month Subtitle = 
    "Month of " & SELECTEDVALUE(Dates[Month & Year])

I used the Months Difference From Today column as a Slicer and in the Visual-level filter, I used Month Difference From Today (For Sorting) to remove all the values where the column value is “9999”.

And I added Selected Month Subtitle as the Subtitle in the Bar Graph Visual

My Drop down:

Hassan Ashas - Workout 005_Posting.pbix (1.3 MB)

[BrianJ]

@tweinzapfel ,

Great workout! Sort of a hybrid between a regular slicer and a period slicer. Never seen this technique before, but I could envision using it in a real-world report.

Normally, per Roche’s Maxim I would push this calculation upstream to Power Query as @KimC did, but given that this is a DAX workout, when in Rome…

As usual, I am rolling with “No CALCULATE, No TI”-style DAX, with my solution resting heavily on the Dates[CurrentMonthOffset] field.

Because the results of this field are fed into the slicer, I added the following calculated column to the Dates table:

Click here for DAX code

Slicer Value = 

VAR __CurMo = MONTH( TODAY() )

VAR __CurYr = YEAR( TODAY() )

VAR __vTable =
 FILTER(
    ALL( Dates ),
    Dates[Month] = __CurMo &&
    Dates[Year] = __CurYr
)

VAR __CurrMoOffset = 
MAXX( __vTable, Dates[CurrMonthOffset] )

VAR __PrevMoOffset = __CurrMoOffset - 1

VAR __Result =
SWITCH( TRUE(),
    Dates[CurrMonthOffset] > __CurrMoOffset, BLANK(),
    Dates[CurrMonthOffset] = __CurrMoOffset, "Current Month (" & Dates[Month & Year] & ")",
    Dates[CurrMonthOffset] = __PrevMoOffset, "Previous Month (" & Dates[Month & Year] & ")",
    Dates[Month & Year]
)

RETURN __Result

• Brian

[Eze]

I did the following;

1. Using DAX, I Created a date slicer table with columns - MonthnYear,Order and Filter (with is the text shown in the slicer) using UNION and a number of series of ADDCOLUMNs for the date periods.
   (

2. I then created a bi-directional relationship between the date slicer table and the main Dates table joining on MonthnYear column.

I don’t think this approach of mine is elegant and as a learning objective I will be studying the Offsets approach which @BrianJ used. Thanks @BrianJ

Dax Code

Dates Filter =

VAR _Today = TODAY()
VAR _PreviousYear = YEAR(TODAY()) - 1

VAR _LastMonth = EOMONTH(_Today, -1)

VAR _LastTwoMonth = EOMONTH(_Today, -2)

VAR _LastThreeMonth = EOMONTH(_Today, -3)

VAR _LastFourMonth = EOMONTH(_Today, -4)

VAR _vtTable = DataTable(“DateRows”, STRING
,{
{“Dates”}

            }  
       ) 

VAR _vtDateSlicer =
UNION
(

     ADDCOLUMNS(           
     _vtTable
           ,"Filter", "Current Month " &  FORMAT(_Today,"mmm") & " " &  CONVERT(YEAR(_Today),STRING)
      ,"Year&Month",CONVERT(YEAR(_Today),STRING) & "0" & CONVERT(MONTH(_Today),STRING),
     "Order", 1
     ),
    
     ADDCOLUMNS(           
    _vtTable
     ,"Filter", "Previous Month " &  FORMAT(_LastMonth,"mmmm")  & " " &  CONVERT(YEAR(_Today),STRING) 
     ,"Year&Month", CONVERT(YEAR(_Today),STRING) & "0" & CONVERT(MONTH(_LastMonth),STRING)
     ,"Order", 2
     ),

    ADDCOLUMNS(              
      _vtTable
     ,"Filter",  FORMAT(_LastTwoMonth,"mmmm")  & " " &  CONVERT(YEAR(_Today),STRING) 
     ,"Year&Month", CONVERT(YEAR(_Today),STRING) & "0" & CONVERT(MONTH(_LastTwoMonth),STRING)
     ,"Order", 3
     ),

    ADDCOLUMNS(              
      _vtTable
     ,"Filter",  FORMAT(_LastThreeMonth,"mmmm")  & " " &  CONVERT(YEAR(_Today),STRING) 
     ,"Year&Month", CONVERT(YEAR(_Today),STRING) & "0" & CONVERT(MONTH(_LastThreeMonth),STRING)
     ,"Order", 4
     ),

    ADDCOLUMNS(              
      _vtTable
     ,"Filter",  FORMAT(_LastFourMonth,"mmmm")  & " " &  CONVERT(_PreviousYear,STRING) 
     ,"Year&Month", CONVERT(_PreviousYear,STRING) &  IF (LEN(CONVERT(MONTH(_LastFourMonth),STRING)) > 1,"","0")   & CONVERT(MONTH(_LastFourMonth),STRING)
     ,"Order", 5
     )

)

RETURN

_vtDateSlicer

[BrianJ]

@Eze ,

While perhaps this is a bit strong…

I generally think he’s right in that bi-directional relationships should be avoided at all costs if there is an alternative. Extremely rarely, there is not (e.g., the use of a period slicer off of your date table), but in 99.9% of cases, there’s another way to accomplish the same objective without going the bidirectional route.

In this case, there are at least three - 1) the Power Query route that @KimC took; 2) adding a calculated column to the Dates table (my approach); or 3) creating a separate disconnected table that you harvest the slicer choice from and use as a filter condition in your DAX measure.

I hope this is helpful.

• Brian

[Anurag]

Hi ,

My submission for this workout

Logic

For this i created two custom column in date table one for the filter and other for sorting the newly created column

Filter = 
VAR _mon= MONTH(NOW())
VAR _yr=YEAR(NOW())
VAr _cur=_yr*100+_mon
VAR _prev= if(_mon=12,(_yr-1)*100+1,_cur-1)
return
if([MonthnYear]=_cur,"Current Month ("&[Month & Year]&")",if([MonthnYear]=_prev,"Previous Month ("&[Month & Year]&")",[Month & Year]))

FilSort = 
VAR _mon= MONTH(NOW())
VAR _yr=YEAR(NOW())
VAr _cur=_yr*100+_mon
VAR _prev= if(_mon=12,(_yr-1)*100+1,_cur-1)
return
if([MonthnYear]=_cur,999999,if([MonthnYear]=_prev,999998,[MonthnYear]))

Workout 005_Posting.pbix (1.3 MB)

Thanks

[Eze]

Thanks @BrianJ that was helpful. I will definitely make sure I avoid hell

[BrianJ]

@Eze ,

Always a good strategy…

[Eze]

Hi @BrianJ
How did you sort/order the list in the Select Month slicer ?

[scancela.90]

Hello All,

Happy to start with DAX Workouts and learn by doing.
My screenshot:

I used a calculated column to create the dropdown options. In the slicer I used that calculated column and filtered “IsAfterToday = False”

DAX for Calculated Column:

Slicer Month & Year = 
SWITCH(
    TRUE(),
    Dates[CurrMonthOffset] = 0, "Current Month ("&Dates[Month & Year]&")",
    Dates[CurrMonthOffset] = -1, "Previous Month ("&Dates[Month & Year]&")",
    Dates[Month & Year]
)

Was it possible in the workout conditions to use a Calculated column?

Regards,
Santiago

[tweinzapfel]

@scancela.90 - definitely acceptable to add a calculated column. When I post my approach to this later this week, that is what I did.

[Theiren]

I started by investigating the date table, and found the “CurrentMonthOffset” column. Assuming it is calculated automatically based on the current time, I decided to utilise it, and created a new column to the date table called “Month Selection”.

Month Selection = 
IF(
        Dates[CurrMonthOffset] > 0, BLANK(), IF(
            Dates[CurrMonthOffset] = 0, "Current Month (" & Dates[Month & Year] & ")", IF(
                Dates[CurrMonthOffset] = -1, "Previous Month (" & Dates[Month & Year] & ")", Dates[Month & Year]
        )
    )
)

To get them in the correct order, I made something “quick and dirty”. I commented out the blank part of the “Month Selection” IF sentence, created a new “CurrMonthOffsetOpposite” column (below), and sorted the “Month Selection” column by the “CurrMonthOffsetOpposite” column.

CurrMonthOffsetOpposite = Dates[CurrMonthOffset] * (-1)

After sorting, I removed the comments from the “Month Selection” IF sentence, and when inserting the slicer, I just filtered out the blanks.

Maybe not the prettiest solution, but it works.

[BrianJ]

@Eze ,

I sorted the calculated slicer column by the monthly offset and then made sure the visual was set as follows.

[Eze]

Many thanks @BrianJ ,

[Hakeem]

Custom Dates = 


var currentMonth = Month(TODAY()) 

var currentYear = YEAR(TODAY())

var result= SWITCH(
                    TRUE(),
                    Dates[Month] > currentMonth && Dates[Year] >= currentYear, -- if month greater than current month & the year is greater than or equal to current year, return blank
                    BLANK(),
                    Dates[Month] = currentMonth && Dates[Year] = currentYear, -- if month equal to current month & year equal to current year, use "Current Month" as prefix
                    "Current Month (" &Dates[Month & Year] &")",
                    Dates[Month] = currentMonth -1 && Dates[Year] = currentYear, -- if month equal to last month & year equal to current year, use "Previous Month" as prefix
                    "Previous Month ("&Dates[Month & Year] &")",
                    Dates[Month & Year]  -- else return Month & Year
)

return

result

I used the MonthNYear column to sort my custom column in descending order.

I filtered the visual to only show values where it is not blank.

Was this allowed? I’m not sure but it worked for me.

[MubarakBabs]

Funny how I took the same approach as you did but the sort doesn’t work

[MubarakBabs]

It took me far more time than I expected, but I came up with something:

I had two calculated columns. Power BI wouldn’t allow me to sort my slicer calculated column using the Monthnyear column, so I had to create a new sorter column where all the blank columns in my calculated column were equally blank. The sorter column contains the same code from dynamic slicer to circumvent circular dependencies

Dynamic month slicer = 
VAR currentmonth = MONTH(TODAY())
VAR currentyear = YEAR(TODAY())
VAR monthconditions =
   SWITCH(TRUE(),
   Dates[Year]< currentyear, (Dates[Month & Year]),
   AND(Dates[Month] < currentmonth-1, Dates[Year] <= currentyear) , (Dates[Month & Year]),
   Dates[Month] = currentmonth, "Current Month ("& Dates[Month & Year] & ")",
   Dates[Month] = currentmonth-1, "Previous Month (" & Dates[Month & Year] & ")"
   )

RETURN monthconditions


Dynamic sorter = 

//this is simply copied and pasted code from the dynamic slicer column. The only relevant code here is in the RETURN clause
VAR currentmonth = MONTH(TODAY())
VAR currentyear = YEAR(TODAY())
VAR monthconditions =
   SWITCH(TRUE(),
   Dates[Year]< currentyear, (Dates[Month & Year]),
   AND(Dates[Month] < currentmonth-1, Dates[Year] <= currentyear) , (Dates[Month & Year]),
   Dates[Month] = currentmonth, "Current Month ("& Dates[Month & Year] & ")",
   Dates[Month] = currentmonth-1, "Previous Month (" & Dates[Month & Year] & ")"
   )

RETURN
 if(ISBLANK(monthconditions), 
    BLANK(),
    Dates[MonthnYear])

[shahadathossen.shajj]

I went with this approach

> 
> Filtering Label =
> VAR _table = SELECTCOLUMNS(
>     Dates,
>     "Month & Year", Dates[Month & Year],
>     "MonthnYear", Dates[MonthnYear],
>     "Filter Label", SWITCH(
>         TRUE(),
>         Dates[Month] > MONTH( TODAY() ) &&
>         Dates[Year] >= YEAR( TODAY() ),
>         BLANK(),
>         Dates[Month] = MONTH( TODAY() ) &&
>         Dates[Year] = year( TODAY() ),
>         "Current MONTH " & Dates[Month & Year],
>         Dates[Month] = MONTH( TODAY() ) - 1 &&
>         Dates[Year] = year( TODAY() ),
>         "Previous MONTH " & Dates[Month & Year],
>         Dates[Month & Year]
>     )
> ) 
> VAR _keepTable = FILTER(
>     SUMMARIZE(
>         _table,
>         [Month & Year],
>         [MonthnYear],
>         [Filter Label]
>     ),
>     [Filter Label] <> BLANK()
> ) 
> 
> RETURN
> _keepTable

[rishug2811]

Here’s my solution where i used two calculated columns, one for Month and Year Calculation, other for sorting.
Since my Calculated Month & Year column was not getting sorted by existed columns, hence needed to calculated another column for sorting.

This is the code for calculating Month & Year in proper format.

Slicer Sel = 

IF(Dates[CurrMonthOffset] <= 0,
        SWITCH(Dates[CurrMonthOffset],
                0, "Current Month (" & Dates[Month Short] & " " & Dates[Year] & ")" , 
                -1, "Previous Month (" & Dates[Month Short] & " " & Dates[Year] & ")", 
                Dates[Month Short] & " " & Dates[Year]
                )
    )

This is the code for sorting column (same as previous with just small adjustments) -

Sorter Sel = 

var month_cond = 
                IF(Dates[CurrMonthOffset] <= 0,
        SWITCH(Dates[CurrMonthOffset],
                0, "Current Month (" & Dates[Month Short] & " " & Dates[Year] & ")" , 
                -1, "Previous Month (" & Dates[Month Short] & " " & Dates[Year] & ")", 
                Dates[Month Short] & " " & Dates[Year]
                )
    )

return
    SWITCH(month_cond,
            BLANK(), BLANK(),
            Dates[MonthnYear])

Thanks to whole Enterprise DNA team for such amazing workouts.!
Workout 005_Posting.pbix (1.3 MB)