Forum Discussion

PBI_newuser's avatar
PBI_newuser
Post Prodigy
4 years ago
Solved

Remove rows based on condition

Hi, I need to calculate the average unit selling price (AUSP) based on the condition below. Sample pbix here. If the total revenue of last year = 0 or total revenue of current year = 0, keep the ro...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi PBI_newuser , 

    Please modify this formula ：Current Year.

     

    Current year =
VAR result1_ =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Append Table' ),
            'Append Table'[next_count] = BLANK ()
                && 'Append Table'[last_count] = BLANK ()
                && [min_] = 3
                && 'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
        ),
        [Revenue Current Year]
    )
RETURN
    result1_

     

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
4 years ago

PBI_newuser , With help date table and time intellignece you can measure like these for thise qtr and last year same qtr (-4) 

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))


Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

 

 

You need a measure like 

=

if(isblank([QTD Sales]) || isblank([Last year same QTD Sales]) , 1,blank() )

 

or

 

if(coalesce([QTD Sales],0) =0 || coalesce([Last year same QTD Sales],0) =0  , 1, blank() )

  • PBI_newuser's avatar
    PBI_newuser
    Post Prodigy
    4 years ago

    Hi, there are some conditions I would exclude from the sum. Sample here. 

    1. If the current monthly revenue for same contract number is the same as last month, then exclude it from the sum. For example, Contract Number 880024048, the revenue for 2019-10, 2019-11 & 2019-12 is same as 2018-10, 2018-11 & 2018-12. The sum of revenue for FY20 should be 0 and revenue last year = 3658.
    2. If the contract start date of a contract number is the next day of a previous contract end date with the same product and same amount, exclude them from the sum. For example, Product B, contract number 880074407 has contract start date the next dat from contract end date of 880049783 and both contract have same amount, then exclude them from the calculation. Only sum the revenue if there is no revenue last year or the revenue last year is not the same as this year.

     

     

     

     

    • v-xiaotang's avatar
      v-xiaotang
      Community Support
      4 years ago

      Hi PBI_newuser 

      I think this is a challenge to solve it, I'll need some time to think about it. Of course, others will help you also.

       

       

      Best Regards,

      Community Support Team _Tang

      If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • PBI_newuser's avatar
    PBI_newuser
    Post Prodigy
    4 years ago

    Hi amitchandak, i tried to create a measure and filter to 1 but it doesn't work. How to modify the measure?