How to get value of KPI from last date

[Marcin82]

Hello,

I guess it is a noob question but i have already spent several hours without a solution, high level of frustration, 

I have KPIs names in rows. Months in colums. The both create filter context as far as I know. I would like to fill all months with KPI values from the last month (let assume that last is december). I tried ALL () etc. but I cant find solution and an answer why it works like that. I created measure like this:

kpi value for last month = 
var lastmonth = lastdate(date[date])

return

CALCULATE(SUM([kpi value]), FILTER(ALL(dates)), date[date] = [lastmonth]).

I thought that it will fill all table with KPI value from december, despite the date filter context provided by colums(there are months)
It does not work. Colums with months still filter the measure. I dont understand why it happens as there is ALL(dates) included.

[daXtreme]

Hi @Marcin82 

Your formula does not work because you use var lastmonth = lastdate(date[date]) and this line just grabs the last date visible in the current context. Please, before you write anything in DAX, check out what a particular function does. Without this knowledge you won't be getting what you want. If you want to grab a value of a measure that is calculated for a period removed from the current one by 1 month, you have to use this formula:

[KPI 1M-] = // KPI one month back
    calculate(
        [KPI],
        // dateadd moves the current set of dates
        // one month back.
        dateadd(Dates[Date], -1, MONTH),
        // If your calendar, Dates, is marked as
        // as a Date Table in the model, this last
        // line is not necessary (but does no harm).
        removefilters( Dates )
    )

[Marcin82]

Thank You, I read a lot but DAX i still hard and not intuitive. I would like to have KPI not from 1 month back but from all dates till the date from a filter context in table. 

[daXtreme · Accepted solution]

// KPI calculated for the whole period
// from the beginning of time in the calendar
// until the last date visible in the current context.
[KPI (until now)] =
var LastVisibleDate = max( Dates[Date] )
var Output = 
    calculate(
        [KPI],
        Dates[Date] <= LastVisibleDate,
        // If your calendar, Dates, is marked as
        // as a Date Table in the model, this last
        // line is not necessary (but does no harm).
        removefilters( Dates )
    )
return
    Output

but maybe you want this:

// KPI for the period from the beginning of time
// until the last date of the previous month.
// Previous month is calculated as the month BEFORE
// the first date in the current context. This is
// how the function PREVIOUSMONTH works.
[KPI (until now)] =
var LastDatePrevMonth = max( previousmonth( Dates[Date] ) )
var Output = 
    calculate(
        [KPI],
        Dates[Date] <= LastDatePrevMonth,
        // If your calendar, Dates, is marked as
        // as a Date Table in the model, this last
        // line is not necessary (but does no harm).
        removefilters( Dates )
    )
return
    Output

[v-chenwuz-msft]

Hi @Marcin82 ,

Please try:

kpi value for last month = 
var lastmonth = month(calculate(lastdate(dates[date]), all(dates)))

return

CALCULATE(SUM([kpi value]), FILTER(ALL(dates)), month(dates[date]) = [lastmonth]).

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.