DAX FUNCTION

[revansh]

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

[SqlJason · Accepted solution]

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