Forum Discussion
DAX FUNCTION
Hi All,
My requirement is to show current month data as a KPI value
Sample Data1:
JAN 0.1
FEB 0.2
Mar 0.3
Apr 0.4
KPI Value = 0.4
Sample Data2:
JAN 0.1
FEB 0.2
Mar 0.0
Apr 0.0
KPI Value = 0.2
If the current month value is 0 , check prio month . If prior month is 0 then look for 2months back value and so on..
Thanks
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.
16 Replies
It would be easier if you had given your model and table structure.
What you have to do is to create a measure
KPI = VAR MaxMonth = CALCULATE ( MAX ( Table[Month] ), FILTER ( ALL ( Table ), Table[Sales] > 0 ) ) RETURN CALCULATE ( SUM ( Table[Sales] ), FILTER ( ALL ( Table ), Table[Month] = MaxMonth ) )You need to add a measure to you table with following formula and use myKPIValue measure to show the value.
myKPIValue = CALCULATE(MAX(Table1[myValue]), FILTER(ALL(Table1), Table1[myValue]>0))
Hi Parry2k,
we cant use max aggregation because value will not be in ascending order by month.
Thanks
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.
