Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX: Conditional Formula - If blank, return previous period value

Hello,  Power BI Gurus, 

 

Can you please help me solve below issues? 

 

I am using Matrix to show my transactions but I am running into issue that some columns returning blank due to no transaction in that period. How would I able to get my cumulative measure pick up the previous cumulative amount (in red)?

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Please move date/period related columns to a separate table(join with your table 1-M)  and use the formula using that table. Also use the period from that new table in the visual column

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

     

    In this case, you should use the period from the date table

  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    Please create the new table and relationship.

     

    Table = VALUES ( LossTransactionDetail[Period] )

     

    Then create the measure.

     

    Measure =
CALCULATE (
    SUM ( LossTransactionDetail[Total_Reserve_Change] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Period] <= MAX ( 'Table'[Period] ) )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Please move date/period related columns to a separate table(join with your table 1-M)  and use the formula using that table. Also use the period from that new table in the visual column

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

     

    In this case, you should use the period from the date table

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    amitchandak , 

     

    Thank you so much for your reply. However, The measure is still show blanks for those years and period that did not have any transactions. How can I make that available for year 1997 period 9 and 10 same as 1997 period 8 (I know period 11 is same as year 8 due to $0 transaction)? 

     

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Anonymous ,

       

      Please create the new table and relationship.

       

      Table = VALUES ( LossTransactionDetail[Period] )

       

      Then create the measure.

       

      Measure =
CALCULATE (
    SUM ( LossTransactionDetail[Total_Reserve_Change] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Period] <= MAX ( 'Table'[Period] ) )
)

       

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
      Best Regards,
      Winniz
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.