Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

First no blank with gorups()

Hi, I need to find last value (based on Date) for each group (Currency) and fill values if missing.

 

Table:

DateCurrencyExchange_rate
01-07-2019

EUR

2.2
01-09-2019EUR2.1
01-01-2020EUR 
03-02-2020EUR2
01-07-2019USD1.1
01-01-2020USD 
01-01-2020USD 

 

The outcome should be (or it can be a new column with old and filled values):

DateCurrencyExchange_rate
01-07-2019

EUR

2.2
01-09-2019EUR2.1
01-01-2020EUR2.1
03-02-2020EUR2
01-07-2019USD1.1
01-01-2020USD1.1
01-01-2020USD1.1

 

 

I believe it can be done easily in PowerBI, probably with firstnoblank(), but I wasn't albe to figure it out 😕 .
Can someone please help me to resolve this problem? Thank you!

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Anonymous 

    Since your Table “Date” column has the same date, you also need to  create an index column in ascending order of date in the advanced editor  .

     

    Then you can create a  calculated column as below:

     

    Column = 
VAR _index =
    IF (
        'Table'[Exchange_rate] <> BLANK (),
        'Table'[Index],
        CALCULATE (
            MAX ( 'Table'[Index] ),
            FILTER (
                'Table',
                'Table'[Currency] = EARLIER ( 'Table'[Currency] )
                    && 'Table'[Index] < EARLIER ( 'Table'[Index] )
                    && 'Table'[Exchange_rate] <> BLANK ()
            )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Exchange_rate] ),
        FILTER ( 'Table', 'Table'[Index] = _index )
    )

     

     

    Please check attached pbix file for more details.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak , 
      thanks for your input. Unfortunatelly it does not suit to this scenario as Exchange_rate is a calculated column with lookupvalue() from another table. I forgot to mention that. This needs to be solved via calculated column also. 

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        5 years ago

        Hi, Anonymous 

        Since your Table “Date” column has the same date, you also need to  create an index column in ascending order of date in the advanced editor  .

         

        Then you can create a  calculated column as below:

         

        Column = 
VAR _index =
    IF (
        'Table'[Exchange_rate] <> BLANK (),
        'Table'[Index],
        CALCULATE (
            MAX ( 'Table'[Index] ),
            FILTER (
                'Table',
                'Table'[Currency] = EARLIER ( 'Table'[Currency] )
                    && 'Table'[Index] < EARLIER ( 'Table'[Index] )
                    && 'Table'[Exchange_rate] <> BLANK ()
            )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Exchange_rate] ),
        FILTER ( 'Table', 'Table'[Index] = _index )
    )

         

         

        Please check attached pbix file for more details.

         

        Best Regards,
        Community Support Team _ Eason
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.