Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Multiply Daily Value by Monthly Value

I have 2 tables, 1 has monthly data and one has daily data.

 

I want to multiply my daily value by my monthly value in a measure so I can later splice the sum of cost per month

 

Sample Data:

 

Monthly Data 
DateRate
1/1/20182
2/1/20183
3/1/20184

 

 

Daily Data  (Desired output)
DateAmountCost
1/1/20181020
1/2/20181122
1/3/20181224
1/4/20181122
2/1/20181030
2/2/20181133
2/3/20181133
2/4/20181030
3/1/20181040
3/2/20181040
3/3/20181144
3/4/20181248
Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Try this:

     

    [Cost] =
SUMX (
    DailyTable,
    DailyTable[Amount] * LOOKUPVALUE (
            MonthlyTable[Rate],
            MonthlyTable[Date], DailyTable[Date]
        )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks for your reply! 

       

      Unfortunately this measure returns [first day of the month] * [monthly rate ] ; not a sum of the cost each day per month

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Anonymous

         

        Try this MEASURE

         

        CostMeasure =
CALCULATE (
    SUM ( MonthlyTable[Rate] ),
    FILTER (
        MonthlyTable,
        MONTH ( SELECTEDVALUE ( DailyTable[Date] ) ) = MONTH ( MonthlyTable[Date] )
    )
)
    * SUM ( DailyTable[Amount] )