Extract values between BeginDate and EndDate then select top 1 where durationInDays is max

Question

Hi comunity ,&nbsp;Would appreciate any help , because i stuck on task for several days . I need tables in SSAS tabular cube 'Aрхив Торговых Точек'(fact table) and 'ТорговаяТочка'(dimension) . They have relationsip through ShopId column .&nbsp; I have also Calendar table which is marked as Date table which has not relationship with&nbsp;'Aрхив Торговых Точек' as there is no appropriate Date column in 'Aрхив Торговых Точек'.&nbsp;I want to create measure in&nbsp;'ТорговаяТочка' table&nbsp;which would extract Shopid which was active during selected month in filters .Conditions are :&nbsp;There are BeginDate ,EndDate ,isArchive and durationInDays columns in&nbsp;'Aрхив Торговых Точек' . So let's say i have selected date November 2016 .I want code to filter out if selected month and year in filter , falls into range of begindate and enddate , so then it would extract the one in range .But there could more than one Shopid in that date range ,so in this case it should extract the one whose duratioInDays is maximum . So for example ,here is the data in ddatabase , as you can see there are multiple values for shopid,where November 2016 falls apart between begindate and enddate . I need to take only that which has the most days in durationInDays column(red marked) and count it as 1.So for each month it would count 1 for only that ShopId which falls into range with the highest value in durationInDays column . I have dax code , which does meet requirements,so for now from the above screenshot it take shopid where duratioIndays = 179 ,for the same filter November 2016 . Thanks in advance .VAR HasFilter =&nbsp; &nbsp; HASONEVALUE('Календарь'[MonthNo])VAR CloseDateFilter =&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALL('Aрхив Торговых Точек'),&nbsp; &nbsp; &nbsp; &nbsp; 'Aрхив Торговых Точек'[isArchive] = 0 &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; 'Aрхив Торговых Точек'[BeginDate] &lt;= max('Календарь'[DayDate]) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; 'Aрхив Торговых Точек'[EndDate] &gt; max('Календарь'[DayDate]) &nbsp; &nbsp;&nbsp; &nbsp; )VAR MaxDurationShop =&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; MAXX(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Aрхив Торговых Точек'[ShopID]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MAX('Aрхив Торговых Точек'[durationIndays]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CloseDateFilter,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Aрхив Торговых Точек'[ShopID])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )VAR CountOfShopsWithConditions =&nbsp; &nbsp; CALCULATE(COUNTROWS('Aрхив Торговых Точек'),FILTER('Aрхив Торговых Точек','Aрхив Торговых Точек'[durationInDays] = MaxDurationShop))RETURN&nbsp; &nbsp; IF(HasFilter, IF(NOT(ISBLANK(MaxDurationShop)), CALCULATE(CountOfShopsWithConditions), BLANK()),BLANK())&nbsp;

chakravarthy · Answer

Anonymous&nbsp;, Create a measure like this.&nbsp;CALCULATE(AVERAGEx(FILTER(Table,Table[BeginDate]&lt;=max('Date'[Date]) &amp;&amp; (ISBLANK(Table[EndDate]) || Table[EndDate]&gt;max('Date'[Date]))),(DATEDIFF(Table[BeginDate],Max('Date'[Date]),Day))),CROSSFILTER(Table[BeginDate],'Date'[Date],None))

anonymous · Answer

Hi ,thanks for your reply . Here is the error message :&nbsp;&nbsp;

Forum Discussion

Extract values between BeginDate and EndDate then select top 1 where durationInDays is max

2 Replies

Related content

Extract Data from sharepoint

Data Extraction from Oracle

Extracting data from External API

split or extract?

Fabric Performance Extraction