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] )
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thank you for the reply Zubair_Muhammad,

         

        this works, but it does not work for when the tables are connected to a common date table unfortunately. Sorry for the misunderstanding.

         

        See pbix file not sure how to upload pbix file,

         

        I have a custom table 

         

        Calendar =
        CALENDAR ("2012-1-1" , "2021-01-01")

         

        with both tables linked to it. Any ideas?