Lastnonblank date - calculated column for each category and date range

[Andreew14]

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.

[rfigtree]

If not solved already try.

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

[amitchandak]

@Andreew14 , Try a new measure like

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

[Andreew14]

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]

@Andreew14 , try like

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

or

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

[Andreew14]

None of two mentioned measures is working. 

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

[amitchandak]

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