MAX function not working as expected

Question

Hi,Let me give a background to the problem I am facing:I have a fact table which is a snapshot table (it basically stores multiple modifications of same OrderID). For Modification date, we have EffectiveDate and then we have another column named as ExpiredDate (which is basically day prior to the next EffectiveDate). So, here is some sample data:OrderID&nbsp; &nbsp; EffectiveDate&nbsp; &nbsp; ExpiredDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DepartDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Quantity1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 Mar 2022&nbsp; &nbsp; &nbsp; &nbsp;5 Apr 2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 Sep 2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 31&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6 Apr 2022&nbsp; &nbsp; &nbsp; &nbsp; 4 Sep 2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 Sep 2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 Sep 2022&nbsp; &nbsp; &nbsp; &nbsp;1 Jan 3000(some future date)&nbsp; &nbsp; &nbsp; 5 Sep 2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 32&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 Apr 2022&nbsp; &nbsp; &nbsp; &nbsp; 1 Jan 3000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 Oct 2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 53&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 Jul 2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 Aug 2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;25 Dec 2023&nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 Aug 2023&nbsp; &nbsp; &nbsp; &nbsp; 1 Jan 3000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 25 Dec 2023&nbsp; &nbsp; &nbsp; &nbsp;3Now, based on such data, I have two create two measures:1. Quantity Current Period -- It would be the Orders "as of Today"(2 Aug 2023) with DepartDate of this year (2023)2. Quantity Prior Period -- It would be Orders as of last year (2 Aug 2022) with DepartDate of last year (2022)I have a calendar table which is connected to depart date and filtering fine. So my Quantity Current Period measure looks like-var filteredTable = FILTER(factTable,DATE(2023,8,2) &gt;= factTable[EffectiveDate]&amp;&amp; DATE(2023,8,2) &lt;= factTable[ExpiredDate])&nbsp;RETURN&nbsp;CALCULATE(SUM(factTable[quantity]), filteredTable)This measure is working as expected.&nbsp;For Prior Period, I tried various approaches, but nothing works and I am not able to figure out why it is happening:I have a column in my calendar table with corrosponding Previous year date, I tried creating an inactive relationship between the departDate of factTable with PreviousYearDate and then using the UseRelationShip function. But it is showing blank values.This is my measure:var filteredTable = FILTER(factTable,DATE(2022,8,2) &gt;= factTable[EffectiveDate]&amp;&amp; DATE(2022,8,2) &lt;= factTable[ExpiredDate])&nbsp;RETURN&nbsp;CALCULATE(SUM(factTable[Quantity]),filteredTable,USERELATIONSHIP(DimCalendar[PreviousYearDate], FactTable[DepartDate]))But, this is giving all blank values and it is taking a lot of time.&nbsp;Then I tried,filter(All(Calendar[Date]), Calendar[Date] &gt;= MIN(PreviousYearDate) &amp;&amp; Calendar[Date] &lt;= MAX(PreviousYearDate))It is also not working. I even tried putting these Min and Max aggregation into different measures and those measures are giving me correct dates.&nbsp;&nbsp;I am not able to figure out a solution. Please help.

anonymous · Answer

Hi&nbsp;Aakash_Ladi&nbsp;
You can try the following measure
&nbsp;
Quantity Prior Period =
CALCULATE (
    SUM ( factTable[Quantity] ),
    DATE ( 2022, 8, 2 ) &gt;= factTable[EffectiveDate],
    DATE ( 2022, 8, 2 ) &lt;= factTable[ExpiredDate],
    DATEADD ( DimCalendar[Date], -1, YEAR )
)

&nbsp;

If it cannot work,&nbsp; can you provide some more sample data or related sample file?
&nbsp;
Best Regards!
Yolo Zhu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

aakash_ladi · Answer

Hi&nbsp;Anonymous,Even DATEADD is not working. Please see the attached link for pbix file for your refrence(FYI I am not able to attach the file here, please see if you can access it from here or let me know if you can guide me how to add attachment).https://shorturl.at/zJW18&nbsp;Regards,Aakash Ladi

anonymous · Answer

Hi&nbsp;Aakash_Ladi&nbsp;
The link cannot open, you can upload the file to onedrive, then share it.
&nbsp;
Best Regards!
Yolo Zhu
&nbsp;

aakash_ladi · Answer

Hi Anonymous&nbsp;,&nbsp;Here is the link to uploaded file-https://drive.google.com/file/d/1ojEVvPvcQhoaayPd-2zaQrrlLSDX_jEI/view?usp=drivesdk&nbsp;Let me know if you need access&nbsp;Regards,Aakash Ladi

anonymous · Answer

Hi&nbsp;Aakash_Ladi&nbsp;
It is not available.
&nbsp;
Best Regards!
Yolo Zhu
&nbsp;

Forum Discussion

MAX function not working as expected

9 Replies

Related content

rank() function not working as expected (NOT USING RANKX)

UNION Function not working as expected

All Function Not Working as expected !!!

dbt job schema not working as expected

ALL() function not working as expected