Forum Discussion

PatrickWong's avatar
PatrickWong
Icon for Helper I rankHelper I
7 years ago
Solved

Multiply Daily Value by Monthly Value and type

I have 2 tables, 1 has monthly data and type and the other has the daily data   I want to multiply my daily value by my monthly value of the correct type in a measure so that I can later splice the...
Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    PatrickWong add following calculated column and that will do it, change column and table name as per your date model.

     

    Rate = Monthly Data Table

    Rate Date = Daily Data Table

     

    Cost = 
    LOOKUPVALUE( Rate[rate], Rate[Date],EOMONTH('Rate Date'[Date],-1)+1,Rate[type], 'Rate Date'[Type]) * 
    'Rate Date'[Amount]
parry2k's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

PatrickWong add following calculated column and that will do it, change column and table name as per your date model.

 

Rate = Monthly Data Table

Rate Date = Daily Data Table

 

Cost = 
LOOKUPVALUE( Rate[rate], Rate[Date],EOMONTH('Rate Date'[Date],-1)+1,Rate[type], 'Rate Date'[Type]) * 
'Rate Date'[Amount]