Multiply Daily Value by Monthly Value

[Anonymous]

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
Date	Rate
1/1/2018	2
2/1/2018	3
3/1/2018	4

Daily Data		(Desired output)
Date	Amount	Cost
1/1/2018	10	20
1/2/2018	11	22
1/3/2018	12	24
1/4/2018	11	22
2/1/2018	10	30
2/2/2018	11	33
2/3/2018	11	33
2/4/2018	10	30
3/1/2018	10	40
3/2/2018	10	40
3/3/2018	11	44
3/4/2018	12	48

[Anonymous]

Try this:

[Cost] =
SUMX (
    DailyTable,
    DailyTable[Amount] * LOOKUPVALUE (
            MonthlyTable[Rate],
            MonthlyTable[Date], DailyTable[Date]
        )
)

[Anonymous]

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]

@Anonymous

Try this MEASURE

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

[Zubair_Muhammad]

@Anonymous

See the attached pbix file

[Anonymous]

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?

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this PBI file.

Hope this helps.