DAX: Use max date period, if not filtered

[jdusek92]

Hello,

I am looking for the best way to achieve this:

I have these columns:

Month_Start	Year	Month	Value
01.01.2020	2020	1	1
01.01.2020	2020	1	2
01.02.2020	2020	2	3

I want a measure - just a simple average of [Value]

But calculated for the latest [Month_Start], if there is no filter on the date period.

Date period could be filtered by [Month_Start]/[Year]/[Month].

If date period is filtered, then calculate the average for the given period (or the latest period, if more selected)

What is the most smooth and low-code way to do this?

Warm regards,

Jakub

[amitchandak]

@jdusek92 , Assuming you can use a date table, try a measure like

Measure =
var _max =maxx(allselected(Date), Date[Date])
var _min = minx(allselected(Date), Date[Date])
Var _Min1 = eomonth(_max,-1)+1
return
if(isfilter(Date), calculate(Average(Table[Value]) , filter(Date, Date[Date]>=_min && Date[Date]<=_max)) ,
calculate(Average(Table[Value]) , filter(Date, Date[Date]>=_min1 && Date[Date]<=_max)) )

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.

[jdusek92]

Hi,

I am not using a date table in this report.

I was thinking, and maybe it would be easier to calculate it always for the max period in the context.

var _max = max([Month_Start])

return calculate(Average([Value]),[Month_Start] = _max)

but it looks too simple, so there might be some catches that I do not see. What do you think?

[Anonymous · Accepted solution]

Hi @jdusek92,

You can try to use the below formula to calculate the filtered date range result or the default max date results:

Measure =
CALCULATE (
    AVERAGE ( Table[Value] ),
    FILTER (
        ALLSELECTED ( Table ),
        IF (
            ISFILTERED ( Table[Month_Start] ) = FALSE (),
            Table[Month_Start] = MAXX ( ALLSELECTED ( Table ), [Month_Start] )
        )
    )
)

Regards,
Xiaoxin Sheng