Forum Discussion
DAX FUNCTION
Assuming that Table1 is your table name, the first column is of date type (and is called Date) and there is a measure called Sales = sum(Table1[KPIValue]), then use the formula below
Test =
VAR LastDateWithSales =
CALCULATE (
MAX ( Table1[Date] ),
FILTER ( ALL ( Table1 ), Table1[Date] <= MAX ( Table1[Date] ) && [Sales] > 0 )
)
RETURN
CALCULATE ( [Sales], Table1[Date] = LastDateWithSales )i got the below result
Is the Region coming from a master (lookup) table or is it part of the same Table? If part of the same table, you can just use an ALLEXCEPT
VAR LastDateWithSales =
CALCULATE (
MAX ( Table1[Date] ),
FILTER ( ALLEXCEPT ( Table1, Table1[Region] ), Table1[Date] <= MAX ( Table1[Date] ) && [Sales] > 0 )This will ensure that the LastDAte is calculated after the filter on Region is done. If Region is coming from a lookup table, you can use the ALLEXCEPT for the field in Table1 that is connecting to the Region lookup table.
Hi SqlJAson,
what change needs to be done if i have to consider YEAR also.... MAX(table[month],table[year])?
KPI =
VAR MaxMonth =
CALCULATE ( MAX ( Table[Month] ), FILTER ( ALL ( Table ), Table[Sales] > 0 ) )
RETURN
CALCULATE (
SUM ( Table[Sales] ),
FILTER ( ALL ( Table ), Table[Month] = MaxMonth )
)Thanks
Ideally, there should be one field in the table which is a combination of Year and Month (if not, you can create it by concatenating year and month). And then use that inside the max.