MAX FUNCTION AND SLICER VALUE

[gabrielefugazzi]

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 filter the dataset is filtered to 2019 and 2020 rows.

I've taken as a leading reference this customer record with selling amount per year extracting it directly from the tabular model I've created.

id                  2020   2019 totale

0201000084       0    8,52  8,52

It's clear that this specific customer has been inactive during 2020 and has performed some buying on 2019.

Since I wanted to buld two formulas to calculate the selling amount during last year and during previous year I've tried the following two formulas (fatturato_ac: selling_amount_last_year, fatturato_ap: selling_amount_previous_year).

Running the dax formula (where year is fixed) per last year and last year-1

fatturato_ac = VAR maxdate = 2020 return

if(isblank(round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2)), 0, round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2))

fatturato_ap = VAR maxdate = 2019 return

if(isblank(round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2)), 0, round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2))

produces the following matrix row

which is the expected result.

But my desire was to use a parametric version of the formula where year is not fixed but directly linked to the selected values in the filter. So I developed the following formulas, keeping this calculation as the max year selector which I'm considering to be the max value in the filter.

VAR maxdate = max(fatturato[anno_fattura]) 

Running the dax formula (where year is not fixed) per last year and last year-1

fatturato_ac = VAR maxdate = max(fatturato[anno_fattura]) 

return

if(isblank(round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2)), 0, round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2))

fatturato_ap = VAR maxdate = max(fatturato[anno_fattura])-1
return

if(isblank(round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2)), 0, round(CALCULATE(sum(fatturato[fatturato]), fatturato[anno_fattura] = maxdate), 2))

Produce an undesired situation in which the max year is selected individually, row by row. Since my example customer has not performed any action this year the max year value is shifted on the previous year.

How can I use the formula correctly to get the results from the formula where the year was fixed by hand but with the use of a parameter?

Thank you in advance

[v-shex-msft · Accepted solution]

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]

@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.