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

[Johan]

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!

[Anonymous · Accepted solution]

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.

[AilleryO]

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