cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xiumi_hou
Post Patron
Post Patron

Need help for dax to search last/next date

Dear all, 

 

I have some issue build dax function:

 

1. I want to search the last date was held, and the condition is status is held and session type is (A or B or C)

   Below is my function which show error : 

Last Session Date =
CALCULATE(
MAX(Calls[date_start]),
FILTER(Calls,
Calls[status]="Held"&&
Calls[type_c]="A"||"B"||"C"
)
)
 

1. I want to search the nearest date is scheduled, and the condition is status=schedule and session type is (A or B or C),

if there is no schedule,  I want it to show "Not available“(I do not know where i should add this)

   Below is my function which show error : 

Last Session Date =
CALCULATE(
MIN(Calls[date_start]),
FILTER(Calls,
Calls[status]="Schedule"&&
Calls[type_c]="A"||"B"||"C"
)
)
1 ACCEPTED SOLUTION





 

Last Session Date =
VAR _CComplete = "Coaching_Completion"
VAR _CSession = "Coaching_Session"
VAR _Assess = "Assessment"
VAR _calc =
    IF (
        Calls[date_start] = null,
        "Not Available",
        CALCULATE (
            MAX ( Calls[date_start] ),
            FILTER (
                Calls,
                Calls[status] = "Held"
                    && Calls[type_c] = _CComplete
                    || _CSession
                    || _Assess
            )
        )
    )
RETURN
    _calc

 

Hi @xiumi_hou , try this.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
xiumi_hou
Post Patron
Post Patron





 

Last Session Date =
VAR _CComplete = "Coaching_Completion"
VAR _CSession = "Coaching_Session"
VAR _Assess = "Assessment"
VAR _calc =
    IF (
        Calls[date_start] = null,
        "Not Available",
        CALCULATE (
            MAX ( Calls[date_start] ),
            FILTER (
                Calls,
                Calls[status] = "Held"
                    && Calls[type_c] = _CComplete
                    || _CSession
                    || _Assess
            )
        )
    )
RETURN
    _calc

 

Hi @xiumi_hou , try this.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Thanks  @Nathaniel_C      I think it nearly reach the correct. What I really want is  if the condition I put can not satisfy ,then it will show "Not available" , it is not a real blank filed. Thank you so much again。

Please see below information:

 

QUESTION.JPG

Hi @xiumi_hou ,
So are you saying, if call status <> "Held" OR if call type <> (one of your conditions), then "Not Available"
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




xiumi_hou
Post Patron
Post Patron

Correct some typing error: 

1. I want to search the last date was held, and the condition is status=held and session type is (A or B or C)

   Below is my function which show error : 

Last Session Date =
CALCULATE(
MAX(Calls[date_start]),
FILTER(Calls,
Calls[status]="Held"&&
Calls[type_c]="A"||"B"||"C"
)
)
 

2. I want to search the nearest date is scheduled, and the condition is status=schedule and session type is (A or B or C),

and if there is no schedule date,  I want it to show "Not available“(I do not know where i should add this)

   Below is my function which show error : 

Next Session Date =
CALCULATE(
MIN(Calls[date_start]),
FILTER(Calls,
Calls[status]="Schedule"&&
Calls[type_c]="A"||"B"||"C"
)
)
Nathaniel_C
Super User I
Super User I

Hi @xiumi_hou ,

Last Session Date =
CALCULATE (
    MAX ( Calls[date_start] ),
    FILTER ( Calls, Calls[status] = "Held" && Calls[type_c] = "A" || "B" || "C" )
)

This is written correctly, but I don't have your tables. Can you supply a PBIX file? Also what is the error message?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you so much for your reply.

Below is the exact function I put. By the way, do you know how to add "Not available" if's there's any date? Thanks!

 

Last Session Date =
CALCULATE(
MAX(Calls[date_start]),
FILTER(Calls,
Calls[status]="Held"&&
Calls[type_c]="Coaching_Completion"||"Coaching_Session"||"Assessment"
)
 
)
 
And below is the error message:

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors