get a table with Min and Max Years upon selecting years from a slicer.

[tamiribas]

Hi

I am trying to get a table with Min and Max Years upon selecting two or more years from a date(Year) slicer.

I need help to get it working.

https://drive.google.com/file/d/1Haf8FMIICZOAiE839trkJgdJN1ZFLKrx/view?usp=sharing 

I have this Table measure:

MinMaxYears =
TREATAS(VALUES(dimDate[Year]),dimDate[Year])

and a slicer based on dimDate[Year]

(see link to file)

Thank you 

Tamir

[Ritaf1983 · Accepted solution]

Hi @tamiribas 
You can add a year column to the new table that you created :

MinMaxYears = values('dimDate'[Year])

Create a flag measure 

flag = if(max(MinMaxYears[Year])=max('dimDate'[Year])|| min('MinMaxYears'[Year])=min('dimDate'[Year]),1,0)

Put the year from the dim date at the slicer, the new table's year at the column on the table, and filter the table by the flag measure:

Result :

PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[tamiribas]

Actually, the following file expresses better the requirement. 

https://drive.google.com/file/d/1sBYqwxXJrXE6fXhngeL_fglpzXFEJ8zP/view?usp=sharing 

The problem:

No matter what I choose in the DimDate[Year] slicer, I always get the first and last year of the dimDate table.

I didn't manage to force MIN Year and MAX year in the VARs.

I am using these values to show in the Calculated table.

SummaryTableMinMaxYears =

VAR _MinYearSelected = CALCULATE(MIN(dimDate[Year]))

VAR _MaxYearSelected = CALCULATE(MAX(dimDate[Year]))

VAR SalesAmountYearMin = CALCULATE(SUM(factSales[Amount]), 'dimDate'[Year] = _MinYearSelected)

VAR SalesAmountYearMax = CALCULATE(SUM(factSales[Amount]), 'dimDate'[Year] = _MaxYearSelected)

VAR TotalAmountYearMin = CALCULATE(COUNTROWS('factSales'), 'dimDate'[Year] = _MinYearSelected)

VAR TotalAmountYearMax = CALCULATE(COUNTROWS('factSales'), 'dimDate'[Year] = _MaxYearSelected)

VAR SalesAmountDiff = SalesAmountYearMax - SalesAmountYearMin

VAR TotalAmountDiff = TotalAmountYearMax - TotalAmountYearMin

RETURN

    UNION(

        ROW("Year", _MinYearSelected, "SalesAmount", SalesAmountYearMin, "TotalAmount", TotalAmountYearMin),

        ROW("Year", _MaxYearSelected, "SalesAmount", SalesAmountYearMax, "TotalAmount", TotalAmountYearMax),

        ROW("Year", "YoY"                   , "SalesAmount", SalesAmountDiff   ,      "TotalAmount", TotalAmountDiff)

    )

Thank you,

Tamir

[Fowmy · Accepted solution]

@tamiribas 

Please check the attached file, I created a measure and assigned at the visual filter on the Filter pane. I think you do not have to create a separate table here. Please check

Min or Max = 
VAR __MinYear = 
    CALCULATE(
        MIN(dimDate[Year]),
        ALLSELECTED( dimDate[Year] )
    )
VAR __MaxYear = 
    CALCULATE(
        MAX(dimDate[Year]),
        ALLSELECTED( dimDate[Year] )
    )
VAR __Result = 
           INT (  SELECTEDVALUE( dimDate[Year] ) in {__MinYear,__MaxYear} )
RETURN
    __Result

[NadavK]

Hi
It's very interesting! I would be happy to see the answer, I also met with a similar task

[Ritaf1983 · Accepted solution]

Hi @tamiribas 
You can add a year column to the new table that you created :

MinMaxYears = values('dimDate'[Year])

Create a flag measure 

flag = if(max(MinMaxYears[Year])=max('dimDate'[Year])|| min('MinMaxYears'[Year])=min('dimDate'[Year]),1,0)

Put the year from the dim date at the slicer, the new table's year at the column on the table, and filter the table by the flag measure:

Result :

PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly