topic Re: get minimum of selected slicer column values, in a table showing the selected column. in DAX Commands and Tips

get minimum of selected slicer column values, in a table showing the selected column.

Johan — Tue, 17 Oct 2023 11:05:49 GMT

slicer: yearmonth

selected: 2023-09 and 2023-10

table

yearmonth | salesvalue

2023-09 | 1234

2023-10 | 4321

what I want to add is the accumulated sales in the selected period.

table

yearmonth | accum.sales

2023-09 | 1234

2023-10 | 5555

for this I need the minimum and maximum of the selected slicer values, to get the filter in dax: yearmonth >= MinYearMonth, yearmonth <= MaxYearMonth.

How to get the Min and MaxYearMonth? Because in the table itself the selectedvalue of yearmonth becomes the single value of the line in the table: either 2023-09 of 2023-10, not both.

Thanks!

Re: get minimum of selected slicer column values, in a table showing the selected column.

AilleryO — Tue, 17 Oct 2023 13:45:47 GMT

Hi,

Not sure to understand exactly, but my guess would be to add an ALL( YearMonth ) in your MAX, to check max without the filters (rows) of your visuals.

Something like :

CALCULATE ( MAX(YearMonth) , ALL(YearMonth) )

but it might be useless...
since your cumulative total could be achieved with :

RunningTotal=
VAR CurrentYM = SELECTEDVALUE( YourTable[YearMonth] )//Get the value on the current line
RETURN
CALCULATE( SUM(YourTable[Amount]),    //You can replace by your measure
ALL( YourTable[YearMonth] ),         //Unfilter the YearMonth from visual
YourTable[YearMonth]<=CurrentYM     //Add only the value previous to the current one
)

Hope it helps, if not let us know what's wrong

Re: get minimum of selected slicer column values, in a table showing the selected column.

Anonymous — Thu, 19 Oct 2023 04:23:54 GMT

Hi Johan ,

Please try below steps:

1. below is my test table

Table:

Table2:

Table 2 = CALENDAR(FIRSTDATE('Table'[Year Month]),LASTDATE('Table'[Year Month]))

2. create measure with below dax formula

Measure = VAR _a = MIN ( 'Table 2'[Date] ) VAR _b = MAX ( 'Table 2'[Date] ) VAR tmp = CALENDAR ( _a, _b ) VAR cur_ym = SELECTEDVALUE ( 'Table'[Year Month] ) RETURN IF ( cur_ym IN tmp, 1 )

Measure2 = VAR cur_ym = SELECTEDVALUE ( 'Table'[Year Month] ) VAR tmp = FILTER ( ALL ( 'Table' ), [Year Month] <= cur_ym && [Measure] = 1 ) RETURN SUMX ( tmp, [Sales] )

3. add a slicer visual with Table 2 field , add a table visual with Table field and Measure2, add Measure to table visual filter pane and set

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.