topic Re: Lastnonblank date - calculated column for each category and date range in DAX Commands and Tips

Lastnonblank date - calculated column for each category and date range

Andreew14 — Mon, 01 Feb 2021 17:13:43 GMT

Hi everyone,

could anybody give me an advice how to create calculate column for lastnonblank date for each category and within the date range, please ?

I have a table where are dates, accounts and balance. Balance is available only for some dates.

I need to calculate date, where the balance is not blank, but for each account separately.

Example account "AAA" has balance in 29.1.2021 and 27.1.2021.

So for date 31.1.2021 and account "AAA" there is a last non blank date : 29.1.2021

But for date 28.1.2021 and account "AAA" there is a last non blank date 27.1.2021

A need this DAX as CALCULATED COLUMN - not measure.

I would be very greatful if someone could help.

amitchandak — Mon, 01 Feb 2021 17:15:55 GMT

Andreew14 , Try a new measure like

calculate(max(Table[Date]), not(isblank(Table[balance])), allexcept(Table, Table[Account]))

Andreew14 — Mon, 01 Feb 2021 17:26:10 GMT

Hi amitchandak,

thank you for replying.

But it not working properly (for account "AAA" in 28.1.2021 , there should be last nonblank date 27.1.2021 and not 29.1.2021).

I need it as calculated column. Could you help?

amitchandak — Mon, 01 Feb 2021 17:39:26 GMT

Andreew14 , try like

calculate(max(Table[Date]), not(isblank(Table[balance])), allexcept(Table, Table[Account]), filter(Table, [Date] <=max(Table[Date])))

calculate(max(Table[Date]), allexcept(Table, Table[Account]), filter(Table, [Date] <=max(Table[Date]) && not(isblank(Table[balance]))))

Andreew14 — Mon, 01 Feb 2021 17:50:08 GMT

None of two mentioned measures is working.

Only dates where is a any balance are showing. Where is no balance , measures are empty.

amitchandak — Tue, 02 Feb 2021 06:56:53 GMT

Andreew14 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

rfigtree — Tue, 02 Feb 2021 07:12:32 GMT

If not solved already try.

=CALCULATE(max(Table1[date]),FILTER(all(Table1),Table1[acct]=EARLIER(Table1[acct])))