Forum Discussion

KatPL's avatar
KatPL
Frequent Visitor
3 years ago

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

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 ?

 

 

 

 

Need Help

7 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try

    Budget Value =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR Result =
    LOOKUPVALUE (
        'Table'[Budget],
        'Table'[Date], DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ), 1 )
    )
RETURN
    Result
  • KatPL's avatar
    KatPL
    Frequent Visitor
    3 years ago

    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's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      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's avatar
        KatPL
        Frequent Visitor
        3 years ago

        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... ? 🤔