MAX function not working as expected

[Aakash_Ladi]

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    EffectiveDate    ExpiredDate                                  DepartDate         Quantity
1               1 Mar 2022       5 Apr 2022                                    5 Sep 2022          3
1               6 Apr 2022        4 Sep 2022                                   5 Sep 2022          2
1                5 Sep 2022       1 Jan 3000(some future date)      5 Sep 2022          3

2                2 Apr 2022        1 Jan 3000                                   2 Oct 2023          5

3                1 Jul 2023          1 Aug 2023                                 25 Dec 2023        2         

3                 2 Aug 2023        1 Jan 3000                                  25 Dec 2023       3


Now, 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) >= factTable[EffectiveDate]

&& DATE(2023,8,2) <= factTable[ExpiredDate]

)

 RETURN

 CALCULATE(SUM(factTable[quantity]), filteredTable)
This measure is working as expected.

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) >= factTable[EffectiveDate]

&& DATE(2022,8,2) <= factTable[ExpiredDate]

)

 RETURN

 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.

Then I tried,
filter(All(Calendar[Date]), Calendar[Date] >= MIN(PreviousYearDate) && Calendar[Date] <= 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. 

I am not able to figure out a solution. Please help.

[Aakash_Ladi]

Hi @v-xinruzhu-msft,

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

Regards,

Aakash Ladi

[v-xinruzhu-msft]

Hi @Aakash_Ladi 

The link cannot open, you can upload the file to onedrive, then share it.

Best Regards!

Yolo Zhu

[Aakash_Ladi]

Hi @v-xinruzhu-msft ,

Here is the link to uploaded file-

https://drive.google.com/file/d/1ojEVvPvcQhoaayPd-2zaQrrlLSDX_jEI/view?usp=drivesdk

Let me know if you need access

Regards,

Aakash Ladi

[v-xinruzhu-msft]

Hi @Aakash_Ladi 

I change your  fulldate column first

Order Date full = DATE(LEFT([OrderDate],4),MID([OrderDate],5,2),RIGHT([OrderDate],2))

Then change the relationship

Then change the PP measure

Qty (PP) = var a= EOMONTH([PointDate],-13)+DAY([PointDate])
return
CALCULATE(SUM(factResSummary[Qty]), a>= factResSummary[EffectiveDate],a <= factResSummary[ExpiredDate],DATEADD(DimCalendar[FullDate],-1,YEAR))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Aakash_Ladi]

Hi @v-xinruzhu-msft ,

Thanks for replying. I believe this will work. Could you please explain what was wrong with the earlier approach?

Regards,

Aakash Ladi

[v-xinruzhu-msft]

Hi @Aakash_Ladi 

The userelationship() function is used between inactive relationship, the relationship between your tables is active, so it is not neccessary to use the function.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xinruzhu-msft]

Hi @Aakash_Ladi 

It is not available.

Best Regards!

Yolo Zhu

[Aakash_Ladi]

Hi @v-xinruzhu-msft,

Try this link-
Sample file.pbix 

or This - https://www.dropbox.com/scl/fi/ypq64mcknnmsj23d4ouu6/Sample-file.pbix?rlkey=ve98q78mfd8p4v4lven2ism4...

Regards,

Aakash

[v-xinruzhu-msft]

Hi @Aakash_Ladi 

You can try the following measure

Quantity Prior Period =
CALCULATE (
    SUM ( factTable[Quantity] ),
    DATE ( 2022, 8, 2 ) >= factTable[EffectiveDate],
    DATE ( 2022, 8, 2 ) <= factTable[ExpiredDate],
    DATEADD ( DimCalendar[Date], -1, YEAR )
)

If it cannot work,  can you provide some more sample data or related sample file?

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.