Add missing rows to table checking another table

[roman_gorbunov]

Hallo,

There is a table with cash balances by day at the end of the day.

This table skips weekends when the balance of money does not change, and I need the missed days to be filled in as well.

I have tried joining this table with the calendar table with this:

Required table =

GENERATEALL (

'Calendar',

VAR CalendarID = 'Calendar'[Date]

RETURN

SELECTCOLUMNS (

CALCULATETABLE ( 'Сash balance ', 'Сash balance '[Date] = CalendarID ),

"Сash balance", 'Сash balance'[balance_sum], "Company", 'Сash balance'[Company]

)

)

But as a result, the weekend is still missed.

What can be done with DAX or M ?

[AllisonKennedy]

@roman_gorbunov  Create a table visual with Calendar[Date] in the table and add a measure:

Value = 

_lastsaledate = MAXX(FILTER(ALL(cashbalancetable), cashbalancetable[date] <= calendar[date]),  cashbalancetable[date] )

RETURN

MAXX( FILTER(ALL(cashbalancetable), cashbalancetable[date] = _lastsaledate,  cashbalancetable[balance_sum] )

[amitchandak]

@roman_gorbunov , try last non Continuous  with help from date table

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

or

CALCULATE(lastnonblankvalues( 'Date'[Date], [sales]) ,filter(ALLSELECTED('Date'),'Date'[Date] =MAX('Date'[Date] ) ) )

or

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[roman_gorbunov · Accepted solution]

Thanks a lot, I have modified your formula and its works as required:

cash balance_with weekends =

if(

[cash balance]=blank(),

CALCULATE (

[cash balance],

FILTER (

ALLSELECTED ( 'Calendar' ),

'Calendar'[Date]

= MAXX (

FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] < MAX ( 'Calendar'[Date]) && [cash balance]<>BLANK() ) , 'Календарь'[Date] )

)

),

[cash balance],

)