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:   ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    You may refer to my solution in this PBI file.

     

    Hope this helps.

     

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
      Community 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] )
      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Anonymous

         

        See the attached pbix file