Forum Discussion

Islam's avatar
Islam
Icon for Helper V rankHelper V
5 years ago

filter dimension tables and make calculation from different dimension tables

Hello Every one

I've attached a pbix test file

i have a fact table and three dimension tables 

each of dimension tables has a relationship with the fact table

i wanna subtract minimum assigning date from nearest higher regular add date and get the difference rounded to year like follows 

differenceregular add dateassign date
001/07/201501/08/2014
101/07/2016 
101/07/2017 
001/07/2018 

 

and in the previous table if it found out that difference is 1 year i wanna time the wage from basic employee details in add rate in regular adds table but i need it like comulative 

for example if i have base wage of 246 pounds and i got three adds for 2016 , 2017 and 2018 with add rate of 7% then the modified wage would be 

2015 = 246 

2016 = 263.22

2017 = 281.65

2018 = 301.36 ...etc

if anyone could help i would be very grateful

 

https://drive.google.com/file/d/1xxsgJyZs3ljDQP4vDjrVK9exOHlF-1F9/view?usp=sharing 

Need Help

10 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi Islam ,

     

    Create the following measure:

    Wage growth =
VAR MinimumYear =
    MINX ( ALLSELECTED ( dtblRegularAdds ), YEAR ( dtblRegularAdds[DecImpDate] ) )
VAR currentyear =
    YEAR ( MAX ( dtblRegularAdds[DecImpDate] ) )
VAR growthRate = 0.07
VAR Result =
    SUM ( dtblBasicEmpInfo[Wage] ) * ( 1 + growthRate ) ^ ( currentyear - MinimumYear )
RETURN
    Result

     

    Result in attach PBIX and image below:

     

     

    • Islam's avatar
      Islam
      Icon for Helper V rankHelper V
      5 years ago

      this is awesome bro but what about the date difference check 

      I mean only for test purpose i modified the assigning date to 1/6/2014 like next picture 

       

      then the difference between assigning date will be a year if compared to DecImpDate Like following picture 

       

       

      so the resulted regular add in 1/7/2015 should be 246*1.05 not just 246 like following picture

       

      I'm sorry i can't understand this measure you provided but if you recommend any tutorials about this issue after modifying the measure to apply the date difference condition

      sorry sir for bothering

       

       

       

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        Hi Islam ,

         

        Sorry but from you calculation in the first post I did not understood that the rate was different and that you had the different date check.

         

        Try the following formula:

        Wage growth = 
VAR MinimumYear =
    MINX ( ALLSELECTED ( dtblassignmentDec[Decision Apply Date] ), YEAR ( dtblassignmentDec[Decision Apply Date]) )
VAR currentyear =
    YEAR ( MAX ( dtblRegularAdds[DecImpDate] ) )
VAR growthRate = SUM(dtblRegularAdds[PromotionRate])
VAR Result =
    SUM ( dtblBasicEmpInfo[Wage] ) * ( 1 + growthRate ) ^ ( currentyear - MinimumYear )
RETURN
    Result

         

         

    • Islam's avatar
      Islam
      Icon for Helper V rankHelper V
      5 years ago

      and also the add rate is not fixed i mean it's not always 7%