Populate value for every date using a measure, not a column

[KatPL]

Hello,

The source data for my model is Analysis Services - SQL server. The model is connected with live connection, which means IT IS NOT possible for me to create columns. I can only create measures, and this is why I am having issues with my task.

I do have a column with Budget values for every month, but values are kept only as 1st day of every month (so value is in 01/01/2022 and the rest 02-31/01/2022 are null). I need to populate this value for every date, (so the same value for 01, 02, 03/01/2022 etc...) but using a measure, not a column.

How to create it, so that it can be shown by date ? Is it possibile to create it with measure ?
Having to create it with column means too many formalities - I have to contact local IT so they build that in SQL server itself, I need to create a ticket, they need to test it in test env, etc. Unfortunately I do not have enough time to do this the formal way.

Any chance measure would allow to do it ?

[KatPL]

@johnt75 : Unfortunately I am getting below error:

---

A table of multiple values was supplied where a single value was expected.

I tried to put this measure on a Table only with Date as a column.

I am getting similar error whenever I am trying to create any measure that in fact should be a column. 

Probably not the reason, but I will ask anyway: the Date column and Budget Value column are located in different tables - could that be the reason it doesn't work ? 

[johnt75]

The budget table has a date too, right? For the first of the month? That column is the one you need to use as the second parameter to LOOKUPVALUE

[KatPL]

Budget table does not have Date column itself. Budget table is connected with Calendar table, which has Date column. However Budget Value and Date are connected with each other. I do not know by which key exactly, as this is built-in in sql cube directly, which I do not have access to, but I can filter Budget value by Date. Just physically/technically these columns are not in one table. 

I was wondering whether this may be the reason the formula you provided is not working... ? 🤔

[johnt75]

If Date is filtering budget then you can probably use a simple MAX('Budget'[Budget value]) and rely on the date from the visual to filter it appropriately.

[KatPL]

Unfoturantely this also does not work - on a chart this measure still gives me Budget value only for 1st of the month as a one blue point, instead of a blue line with the same value for every day:

BUD Value = MAX(Budget[Budget Value])

[johnt75]

Try

BUD Value =
CALCULATE ( MAX ( 'Budget table'[BUD Value] ), STARTOFMONTH ( 'Date'[Date] ) )

[johnt75]

Try

Budget Value =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR Result =
    LOOKUPVALUE (
        'Table'[Budget],
        'Table'[Date], DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ), 1 )
    )
RETURN
    Result