Due in next 30 days calculation

[Anonymous]

This is in continuation with Re: Calculation Of Due in Next 30 days - Microsoft Fabric Community

I am trying to calculate list of documents which are Due in Next 30 days (Including Today).
The condition is either [Due In Date] OR [Forecast Submission Date] should be in next 30 days and I am trying the below DAX.

As suggested by @Ahmedx , I am using the below DAX which is quite right-

Due from Supplier in next 30 days = 
VAR _toDay = TODAY ()
VAR _End = _toDay+30
RETURN
CALCULATE (
    [Total Documents],
     'Merged Table'[Actual In] = BLANK () ,
      OR (
        'Merged Table'[Due In]>=_toDay && 'Merged Table'[Due In]<=_End,
        'Merged Table'[Forecast Submission Date]>=_toDay &&  'Merged Table'[Forecast Submission Date]<=_End
    ))

However I need to cover one more condition here is- Latest of (Forecast Date >= today +30 and Forecast date <= today+30) OR (Due in Date >= today+30 and Due in date <= today+30)
To be more clear, we need to check both (Forecast Date & Due in date) and whichever of them is latest, should be within 30 days.
Suggestions please.

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Measure = 
VAR _toDay = TODAY ()
VAR _End = _toDay+30
var _maxdate=IF(MAX('Merged Table'[Forecast Submission Date])>=MAX('Merged Table'[Due In]),MAX('Merged Table'[Forecast Submission Date]),MAX('Merged Table'[Due In]))
RETURN 
CALCULATE (
    [Total Documents],
     'Merged Table'[Actual In] = BLANK () &&
     _maxdate >= _toDay && _maxdate<=_End
    )

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Ahmedx]

I'm not sure I understand your problem, but try it

Due from Supplier in next 30 days = 
VAR _toDay = TODAY ()
VAR _End = _toDay+30
RETURN CALCULATE (
CALCULATE (
    [Total Documents],
     'Merged Table'[Actual In] = BLANK () ,
        MAXX({CALCULATE(Max('Merged Table'[Due In])), CALCULATE(Max('Merged Table'[Forecast Submission Date]))},[Value])>=_toDay && MAXX({CALCULATE(Max('Merged Table'[Due In])), CALCULATE(Max('Merged Table'[Forecast Submission Date]))},[Value])<=_End
    )

[Anonymous]

wondering what's this [Value]

[Ahmedx]

in the value we get the maximum date and then compare it with 30 days

[Anonymous]

Okay. Thanks for all your help.

I am trying this however getting an error as - 
The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

[Ahmedx]

Due from Supplier in next 30 days = 
VAR _toDay = TODAY ()
VAR _End = _toDay+30
RETURN CALCULATE (
CALCULATE (
    [Total Documents],
     'Merged Table'[Actual In] = BLANK () ,
        MAX({CALCULATE(Max('Merged Table'[Due In])), CALCULATE(Max('Merged Table'[Forecast Submission Date])))>=_toDay && MAX(CALCULATE(Max('Merged Table'[Due In])), CALCULATE(Max('Merged Table'[Forecast Submission Date]))<=_End
    )

[Anonymous]

No luck.
A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[Ahmedx]

pls try

Due from Supplier in next 30 days = 
VAR _toDay = TODAY ()
VAR _End = _toDay+30
VAR _Flag = 
RETURN CALCULATE (
CALCULATE (
    [Total Documents],
     'Merged Table'[Actual In] = BLANK () ,
        'Merged Table'[Forecast Submission Date]>=_toDay &&  'Merged Table'[Forecast Submission Date]<=_End
    ), 'Merged Table'[Due In]>=_toDay && 'Merged Table'[Due In]<=_End)

[Anonymous]

@Ahmedx Sorry, but this is not working.
Seems like you missed for VAR Flag as it is incomplete.

Not sure how, but I think we need to compare Due In date & Forecast Date to see which one is greatest/latest.

And then we'll have to check if the greatest of them falls in the range of next 30 days.

[Ahmedx]

I'm trying my best to understand what is needed, but I don't understand what is needed.
Can you post an example with karinkomi and show me what needs to be done?

[Anonymous]

@Ahmedx 

I'll help you understand the situation. 

Please take  a look at the below snap and you'll notice that for the given rows, Due In date is in June'24 however, the Forecast Submission Date is in this month only(which is in next 30 days)

What we need to do here is, we need to check the greatest of Due In date or Forecast date and see if the greatest one falls in the range of next 30 days.

Here as Due In date doesn't fall in the range of next 30 days, these should be excluded from the output.