Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculate at row level then Aggregate in a Measure

Hi everyone,

 

So my table looks like this

AccountNumberFCTypePeriod
A23234$532$85I12/31/2019
A56544$3,323$73C12/31/2019
A84736$812$22C12/31/2019
A99384$729$99C12/31/2019
A38394$3,524$2,363I12/31/2019
A23234$539$95I2/29/2020
A56544$3,333$72C2/29/2020
A84736$812$85I2/29/2020
A38394$3,434$2,323I2/29/2020

 

 

We get new account data every month end or so, we always want our comparisons compared to the last quarter end data. So in this instance 2/29 is our most recent period and we're comparing to 12/31. If the period is 5/31 then we'd compare it to 3/31, etc etc

 

This is the main formula I want to apply for each account, then add it all up for one final #

(Current Period [F] - Prior Period [F]) * (Prior Period [C] / Prior Period [F] )

 

Issues I'm having 

  • If an account closed between the 2 periods, it won't be on 2/29 but I still want the formula applied to it, it'd just essentially be
     ( 0 - Prior Period [F]) * (Prior Period [C] / Prior Period [F] )
  • If an account is type I and stays type I, I don't care about it. Only C's that stay C's and C's that become I's.

 

 

Any help would really be appreciated here, pulling my hair out trying to figure it out, thanks!

Need Help

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    HI  Anonymous 

    For your case, you could try this way as below:

    Step1:

    Add two dim tables and create two relationships as below:

    Dim AccountNumber = VALUES('Table'[AccountNumber])

Dim Period = VALUES('Table'[Period])

    Step2:

    Create a measure as below:

    Measure = 
VAR _CurrentPeriodF= CALCULATE(SUM('Table'[F]))
var _PriorPeriodF= CALCULATE(SUM('Table'[F]),FILTER(ALL('Dim Period'),DATEDIFF('Dim Period'[Period],MAX('Dim Period'[Period]),MONTH)=2))
VAR _PriorPeriodC= CALCULATE(SUM('Table'[C]),FILTER(ALL('Dim Period'),DATEDIFF('Dim Period'[Period],MAX('Dim Period'[Period]),MONTH)=2))
RETURN
(_CurrentPeriodF-_PriorPeriodF)*(_PriorPeriodC/_PriorPeriodF)

     

    If an account is type I and stays type I, I don't care about it. Only C's that stay C's and C's that become I's.

    Do you mean you want to add a conditional that type is "C"?

    for example:

    Measure 2 = 
VAR _CurrentPeriodF= CALCULATE(SUM('Table'[F]),FILTER('Table','Table'[Type]="C"))
var _PriorPeriodF= CALCULATE(SUM('Table'[F]),FILTER('Table','Table'[Type]="C"),FILTER(ALL('Dim Period'),DATEDIFF('Dim Period'[Period],MAX('Dim Period'[Period]),MONTH)=2))
VAR _PriorPeriodC= CALCULATE(SUM('Table'[C]),FILTER('Table','Table'[Type]="C"),FILTER(ALL('Dim Period'),DATEDIFF('Dim Period'[Period],MAX('Dim Period'[Period]),MONTH)=2))
RETURN
(_CurrentPeriodF-_PriorPeriodF)*(_PriorPeriodC/_PriorPeriodF)

    Here is sample pbix file, please try it.

     

    If not your case, please share your expected output based on the sample data.

     

    Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so so so much! Your sample file is very very close to what I need.

       

      Here is what the calculations should be for each account

      AccountNumberFCTypePeriodCalculationNotes
      A23234$532$85I12/31/2019  
      A23234$539$95I2/29/20200Account was type I in previous quarter so formula doesn't apply
      A38394$3,524$2,363I12/31/2019  
      A38394$3,434$2,323I2/29/20200Account was type I in previous quarter so formula doesn't apply
      A56544$3,323$73C12/31/2019  
      A56544$3,333$72C2/29/20200.219681011(_CurrentPeriodF-_PriorPeriodF)*(_PriorPeriodC/_PriorPeriodF)
      A84736$812$22C12/31/2019  
      A84736$812$85I2/29/20200.000000(_CurrentPeriodF-_PriorPeriodF)*(_PriorPeriodC/_PriorPeriodF)
      A99384$729$99C12/31/2019-99(0 -_PriorPeriodF)*(_PriorPeriodC/_PriorPeriodF)
           -98.780319Final Number I want

       

      Differences to what is in the sample pbix

       
      • Measure 2 which filters for type C in previous period doesn't seem to be working? No row level #s are popping up
      • The total numbers are doing the calculation at the aggregate level, while I'd like the row level calculations to be added instead. Difference for Measure 1 is 2404.55 in sample file vs -98.78 above
      • For account A84736 there seems to be an issue since it changed from C to I, i still want the calc applied to that account, it's creating 3 rows something is happening there
      • For most of these reports I'm going to have a slicer at the top where I can look at one certain period, when I do that in the report it makes all the values 0? 
      • It looks like you're DATEDIFF is comparing one date and then adding 2 months to it, I always want it compared to the previous quarter end period, i tried doing something like ENDOFQUARTER(PREVIOUSQUARTER(PERIOD)) but I keep getting errors. This table should help explain better?
      Period I'm filtering onPeriod to Compare Against
      1/31/202012/31/2019
      2/29/202012/31/2019
      3/31/202012/31/2019
      4/30/20203/31/2020
      5/31/20203/31/2020
      6/30/20203/31/2020

       

       

      Thanks again, I can't even put into words how much this has helped already v-lili6-msft