Calculate measure with multiple dates

[Pbi07]

I am struggling with a calculation of measure with 2 dates. 

My table below has 2 dates.  Trying to calculate sum of quantity by revision dates. My filter is on Calendar date which has an active relation with revision date. 

expected result

Sum for selected date of oct15 , should be only 35( excluding the  6th Nov Purch Date ) . Basically, depends on the calendar day selection, total should be only with Purch date of Selected date month. I tried using userelationship, but that didn't work. 

Any advice will help. 

My pbix is https://drive.google.com/file/d/1cMeCbzFtPWDP-ABJ0itxiwnlc_T2QIFr/view?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[TomMartens · Accepted solution]

Hey @Pbi07 ,

next to the solution provided by @Ashish_Mathur , here is another approach that only makes use of the tables

• Revision Table
• Calendar

Tom = 
var MAXSelectedDate = MAX('Calendar'[Date] )
var SelectedYear = YEAR( MAXSelectedDate )
var SelectedMonth = MONTH( MAXSelectedDate )
var EoMofSelectedDate = EOMONTH( MAXSelectedDate , 0 )
var __AllPurchDatesInSelectedMonth = DATESBETWEEN( 'Calendar'[Date] , DATE( SelectedYear , SelectedMonth , 1) , EoMofSelectedDate )
return
CALCULATE(
    SUM( 'Revision Table'[Quantity] )
    , 'Revision Table'[Purch Date] IN  __AllPurchDatesInSelectedMonth
)

A little screenshot:

Hopefully, this provides what you are looking for.

Regards,

Tom

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.