Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate measure with multiple dates

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

Need Help
  • TomMartens's avatar
    TomMartens
    4 years ago

    Hey Anonymous ,

     

    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

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    4 years ago

    Hey Anonymous ,

     

    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