Forum Discussion

zenisekd's avatar
zenisekd
Super User
3 years ago
Solved

Displaying parameter value in specific date

Hi, I am really struggling here, so after a while, I have decided to write here. 

I have created a stock balance measure, which displays a running total of stock values (basically current level + purchases - orders). My client however asked me if I was able to add there some kind of forecasting option, where he would input values, which would influence the balance. 

After some thinking, I came up with the idea of adding 12 parameters, each representing a forecast for a month(today())+1 +2 +3 etc. So he could adjust the forecast for the next 12 months. However, I have run into troubles with the performance. After I have added just one parameter, it went slow... so adding 12 parameters would make one jump of the window probably.  

I am probably doing something wrong when adding the parameter to the specific month (to be more specific the first day of the month).


My measure is:

forecast =
SUMX (
'Date table',
IF (
FIRSTNONBLANK ( 'Date table'[Date], 1 )
= DATE ( IF ( MONTH ( TODAY () ) = 12, YEAR ( TODAY () ) + 1, YEAR ( TODAY () ) ), MONTH ( TODAY () ) + 1, 1 ),
'Parametr'[parametr value]
)
)


Could somebody please advise a better way or suggest a better way to assign the parameter value to the specific date (first day of the month (month of today +1))? Thanks!
  • johnt75's avatar
    johnt75
    3 years ago

    One possibility would be to add a date column to each of the parameter tables, defined as

    EOMONTH( TODAY(), 0) + 1

    for the first month, then just increment the 0 each time for subsequent months. Link the date table to the parameter table and then your stock balance measure would be

    Stock balance = [Current level] + [Purchases] - [Orders] + [Parameter 1] + [ Parameter 2]

    with all the parameters included. 

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    One possibility would be to add a date column to each of the parameter tables, defined as

    EOMONTH( TODAY(), 0) + 1

    for the first month, then just increment the 0 each time for subsequent months. Link the date table to the parameter table and then your stock balance measure would be

    Stock balance = [Current level] + [Purchases] - [Orders] + [Parameter 1] + [ Parameter 2]

    with all the parameters included. 

    • zenisekd's avatar
      zenisekd
      Super User
      3 years ago

      Thanks! I am not sure if it is the most effective solution, but it solves the problem, although now I have there additional 12 tables with 1000 rows each... 😕