Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional fill in formula

I ham having issues with getting the fill in down function working properly.

I have a situation where I need to reset the filling at year end and restart it as soon as the next value in the new year is available

 

The formula that fills in down the data is as follow:

Filledin =
var Last = CALCULATE(LASTNONBLANK(NEwtab[Index],1), filter( all(NEwtab) , NEwtab[Index] <= Earlier (NEwtab[Index]) && NOT(ISBLANK (NEwtab[Blank]))))
var filled = CALCULATE(Sum(NEwtab[Blank]), filter(all(NEwtab), NEwtab[Index] = Last))
return filled
 
Is there anyway to add addition condition to get the desired results?

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please try the below.

    It is for creating a new column.

     

    Filledin NEW CC =
VAR _currentyear = NEwtab[Year]
VAR _currentindex = NEwtab[Index]
VAR _lastnonblankindexinsameyear =
    MAXX (
        FILTER (
            NEwtab,
            NEwtab[Year] = _currentyear
                && NEwtab[Index] <= _currentindex
                && NEwtab[Blank] <> BLANK ()
        ),
        NEwtab[Index]
    )
RETURN
    SUMMARIZE (
        FILTER (
            NEwtab,
            NEwtab[Year] = _currentyear
                && NEwtab[Index] = _lastnonblankindexinsameyear
        ),
        NEwtab[Blank]
    ) + 0

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please try the below.

    It is for creating a new column.

     

    Filledin NEW CC =
VAR _currentyear = NEwtab[Year]
VAR _currentindex = NEwtab[Index]
VAR _lastnonblankindexinsameyear =
    MAXX (
        FILTER (
            NEwtab,
            NEwtab[Year] = _currentyear
                && NEwtab[Index] <= _currentindex
                && NEwtab[Blank] <> BLANK ()
        ),
        NEwtab[Index]
    )
RETURN
    SUMMARIZE (
        FILTER (
            NEwtab,
            NEwtab[Year] = _currentyear
                && NEwtab[Index] = _lastnonblankindexinsameyear
        ),
        NEwtab[Blank]
    ) + 0
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Jihwan, it worked like a charm.

    Would you mind to explain a little bit the logic behind the solution?

    Many thanks.

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago

      Hi,

      Thank you for your message.

      Please kindly check the below. I hope it explains well, or please let me know.

       

      Filledin NEW CC =
VAR _currentyear = NEwtab[Year]
VAR _currentindex = NEwtab[Index]
VAR _lastnonblankindexinsameyear =
    MAXX (
        FILTER (
            NEwtab,
            NEwtab[Year] = _currentyear // same year as the current row
                && NEwtab[Index] <= _currentindex // index number is lower or equal than the current row
                && NEwtab[Blank] <> BLANK () // blank column is not zero
        ),
        NEwtab[Index] // maximum index number that suits the above condition
    )
RETURN
    SUMMARIZE (
        FILTER (
            NEwtab,
            NEwtab[Year] = _currentyear
                && NEwtab[Index] = _lastnonblankindexinsameyear
        ),
        NEwtab[Blank]
    ) + 0
// adding zero in order to detect the row that the starting index number of the year is zero.
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Many thank,

        very much appreciated.🙏