Forum Discussion

newgirl's avatar
newgirl
Post Patron
3 years ago
Solved

Advanced filtering

Hello   I have a sample data model with my current project like below. Data tables are: Activity table - records activities made by salespersons Creation table - records when a client was create...
Need Help
  • BrianConnelly's avatar
    BrianConnelly
    3 years ago

    Need to update the code as such using variables...

    Volume_related = 
VAR startDate = MIN('Calendar'[Date])
VAR endDate = MAX('Calendar'[Date])
RETURN CALCULATE([Volume],USERELATIONSHIP('Calendar'[Date],'CR tbl_phrawsales'[HisSapZ0123_GEOLOC.CreatedOn])
,InvoiceDate >= startDate && InvoiceDate <= endDate
)
newgirl's avatar
newgirl
Post Patron
3 years ago

Hello amitchandak !

 

I tried what you suggested in my actual working file:

 

This is the relationship:

 

And this is the new measure (Vol_Cust) 

Volume_related = CALCULATE([Volume],USERELATIONSHIP('Calendar'[Date],'CR tbl_phrawsales'[HisSapZ0123_GEOLOC.CreatedOn]))

 

I tried to filter how the measure got 45. I understand how it works but...

How can I filter it further to get 22.4? The logic I want to apply is that since the date slicer in the page is filtered to Feb 1 to Feb 28, the measure should calculate for the sum of the volume filtered to the 'CreatedOn' date and the 'Invoice Date'. Hope you can help me how the measure can be improved.

BrianConnelly's avatar
BrianConnelly
Resolver III
3 years ago

Need to update the code as such using variables...

Volume_related = 
VAR startDate = MIN('Calendar'[Date])
VAR endDate = MAX('Calendar'[Date])
RETURN CALCULATE([Volume],USERELATIONSHIP('Calendar'[Date],'CR tbl_phrawsales'[HisSapZ0123_GEOLOC.CreatedOn])
,InvoiceDate >= startDate && InvoiceDate <= endDate
)