Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Improve this measure?

Hi there,

 

I'm currently using this measure to calculate the receivables by year-period: 

 
Invoice amount outstanding  = CALCULATE(SUMX(FACT_rows,IF(SELECTEDVALUE(FACT_rows[year-period]) <= [selected yearperiod] && [Selected matchdate before selected date Y/N invoice] = FALSE() , [Invoice_amount])),FILTER(FACT_rows, FACT_rows[year-period] <= [selected yearperiod]))
 
However, I think the SUMX slows down the performance. It takes like 10 seconds to load. Does someone know a good alternative for this measure but with the same result ofcourse?
 
Thank you
Need Help

4 Replies

  • ALLUREAN's avatar
    ALLUREAN
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi, Anonymous 

     

    You can try using variables for SELECTEDVALUE part, also SWITCH(TRUE() instead of IF in calculation. 

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

    Anonymous , try a measure like

     


    sumx(filter(FACT_rows, max(FACT_rows[year-period]) <= [selected yearperiod] && [Selected matchdate before selected date Y/N invoice] = FALSE() ),[Invoice_amount])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you! And what should it be with this extra feature : 

      IF(SELECTEDVALUE(FACT_rows[year-period]) <= [selected yearperiod] && [SD matchdate before sd Y/N invoice] = FALSE() , [Invoice_amount],IF([max matchstatus available] = 1 && MONTH(datee) >= MONTH(datee ) ,CALCULATE( SUM(FACT_rows[Invoice_amount_outstanding])),BLANK()))
      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous 

         

        Try this measure. Remove && MONTH(datee) >= MONTH(datee ) as it's always true. 

        IF (
    SELECTEDVALUE ( FACT_rows[year-period] ) <= [selected yearperiod]
        && [SD matchdate before sd Y/N invoice] = FALSE (),
    [Invoice_amount],
    IF (
        [max matchstatus available] = 1,
        SUM ( FACT_rows[Invoice_amount_outstanding] ),
        BLANK ()
    )
)

         

        Best Regards,
        Community Support Team _ Jing