Forum Discussion

gabrielefugazzi's avatar
gabrielefugazzi
Icon for Helper I rankHelper I
5 years ago
Solved

MAX FUNCTION AND SLICER VALUE

My report contains a filter (titled ANNO) on which the user is able to select specific years from the data. The currenly selected value is 'Ultimi 2 anni' which means 'Last two years'. Applying this ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi gabrielefugazzi,

    It seems like you are facing the find out the previous record in the discontinued table based on conditions, right?

    If this is the case, I think you can extract the current date and calculate with a fixed numerical value should not suitable for our conditions.

    You need to use condition and current date to filter on your table records to get a list of records, then extract the maximum one of them and this one should meet the previous records requirement.

     

    Measure=
VAR currdate =
    MAX ( fatturato[anno_fattura] )
VAR prevdate =
    CALCULATE (
        MAX ( fatturato[anno_fattura] ),
        FILTER (
            ALL( fatturato ),
            [anno_fattura] < currdate
                && 'other conditions'
        )
    )
RETURN
    'your formula'

     

    For measure on total level calculations, you can refer to the below blog:

    Measure Totals, The Final Word 

    Regards,

    Xiaoxin Sheng

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

gabrielefugazzi , Not very clear to me. Have tried time intelligence with date table

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.