topic Re: Generic date in DAX Commands and Tips

Generic date

Romani — Fri, 19 Jul 2024 02:18:30 GMT

CUSTOMER_NAME	Offer Id	OFFERS_DATE	OFFER_ACCEPTANCE_DATE	REJECTION_DATE	INVESTIGATION_DATE	SUBMISSION_TO_RISK	SUBMISSION_DATE_TO_CREDIT	APPROVAL_DATE
Client 2	1	13/02/2023				15/02/2023	14/02/2023	14/02/2023
Client 3	2	11/12/2023				12/03/2023	22/11/2023	24/12/2023
Client 7	3	09/06/2023	20/09/2023		22/08/2023	10/01/2023	24/09/2023	09/04/2023
Client 9	4	28/08/2023	30/08/2023		22/08/2023	09/05/2023	09/04/2023	09/04/2023
Client 11	5	04/03/2023	06/07/2023		06/07/2023
Client 12	6	21/12/2023	17/01/2024		26/12/2023	20/02/2024	20/02/2024	19/02/2024
Client 14	7	04/10/2023	19/04/2023		04/09/2023	05/09/2023	24/05/2023
Client 15	8	04/06/2023	04/06/2023
Client 18	9	12/04/2023			12/05/2023
Client 20	10	21/12/2023	18/01/2024		27/12/2023	22/01/2024	21/01/2024	21/01/2024
Client 27	11	09/01/2023	24/09/2023		09/10/2023	12/05/2023	12/04/2023	11/02/2023
Client 31	12	24/10/2023		12/04/2023
Client 32	13	01/03/2023	22/01/2023		15/11/2022	14/02/2023	23/01/2023	20/01/2023
Client 38	14	16/11/2023				21/11/2023	21/11/2023	16/11/2023
Client 43	15	03/05/2023	03/05/2023		22/02/2023	03/05/2023	03/05/2023	03/06/2023
Client 44	16	09/03/2023	24/09/2023		09/11/2023	10/12/2023	10/02/2023	10/09/2023
Client 49	17	10/03/2023	10/03/2023		29/08/2023	11/08/2023	10/11/2023	10/11/2023
Client 50	18	09/01/2023	24/09/2023		24/08/2023	26/09/2023	26/09/2023	26/09/2023
Client 51	19	24/09/2023				11/12/2023	11/09/2023	26/09/2023
Client 60	20	10/10/2023		20/11/2023		10/10/2023	20/11/2023

I have all these dates

To explain the situation i want to have dax or calculated column to give the below desired out put :

1- the total number of offers presented in specific period and this i can get it from the total offers date to COUNT it

2- if the total number of offers in january equal 10 so i want something to see the last step that the offer is stpped on it so for example should be 2 at offering first stage 3 are pending accceptane 5 are under investigation that could happen when trackin the different dates of this offer

Re: Generic date

BeaBF — Fri, 19 Jul 2024 12:57:15 GMT

Romani let's nstart from point 1. which is the start date and end date of the period?

The measure is:

Total Offers in Period =

VAR StartDate = DATE(2023, 1, 1) // Set your desired start date

VAR EndDate = DATE(2023, 12, 31) // Set your desired end date

RETURN

COUNTROWS (

FILTER (

YourTable,

YourTable[OFFERS_DATE] >= StartDate &&

YourTable[OFFERS_DATE] <= EndDate

)

you have to set the start date and end date.

BBF

Re: Generic date

Romani — Fri, 19 Jul 2024 13:06:05 GMT

step one achieved
what about step 2

Re: Generic date

BeaBF — Fri, 19 Jul 2024 13:21:27 GMT

Romani perfect! please explain the second step better, with an examploe on data

BBF

Re: Generic date

Romani — Fri, 19 Jul 2024 16:02:14 GMT

Summary

Total Offers: 5
Under Investigation: 1 (Client 11)

Submission to Risk Phase

Clients whose offers ended in this phase:
- Client 32
- Client 44

Submission to Credit Phase

Clients whose offers ended in this phase:
- Client 85

Approval Phase

Clients whose offers ended in this phase:
- Client 49
  So based above info when iam chossing this period give me this result
  did you get my point ?

Re: Generic date

Romani — Sun, 21 Jul 2024 08:06:17 GMT

BeaBF

Re: Generic date

Anonymous — Tue, 23 Jul 2024 02:48:42 GMT

Hi, Romani

You can try the following methods.

Measure:

Total Offers = CALCULATE(COUNT('Table'[CUSTOMER_NAME]),FILTER(ALL('Table'),[OFFERS_DATE]>=MIN('Date'[Date])&&[OFFERS_DATE]<=MAX('Date'[Date])))

Under Investigation = CALCULATE(COUNT('Table'[CUSTOMER_NAME]),FILTER(ALL('Table'),[OFFERS_DATE]>=MIN('Date'[Date])&&[OFFERS_DATE]<=MAX('Date'[Date])&&[SUBMISSION_TO_RISK]=BLANK()))

Measure = IF(SELECTEDVALUE('Table'[OFFERS_DATE])>=MIN('Date'[Date])&&SELECTEDVALUE('Table'[OFFERS_DATE])<=MAX('Date'[Date]),1,0)

I can do this part. For other desired outcomes, you need to elaborate on the logic.

Best Regards,

Community Support Team _Charlotte

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

Re: Generic date

BeaBF — Tue, 23 Jul 2024 07:19:10 GMT

Romani second step:

Total Offers = COUNTROWS('Table')

Under Investigation =
CALCULATE(
COUNTROWS('Table'),
'Table'[OfferStatus] = "Under Investigation"
)

Clients Submission to Risk =
CALCULATE(
COUNTROWS('Table'),
'Table'[SUBMISSION_TO_RISK] <> BLANK(),
'Table'[SUBMISSION_DATE_TO_CREDIT] = BLANK(),
'Table'[APPROVAL_DATE] = BLANK(),
'Table'[REJECTION_DATE] = BLANK(),
'Table'[INVESTIGATION_DATE] = BLANK()
)

Clients Submission to Credit =
CALCULATE(
COUNTROWS('Table'),
'Table'[SUBMISSION_DATE_TO_CREDIT] <> BLANK(),
'Table'[APPROVAL_DATE] = BLANK(),
'Table'[REJECTION_DATE] = BLANK(),
'Table'[INVESTIGATION_DATE] = BLANK()
)

Clients Approval =
CALCULATE(
COUNTROWS('Table'),
'Table'[APPROVAL_DATE] <> BLANK()
)

If you want Client name:
Client Names Under Investigation =
CONCATENATEX(
FILTER(
'Table',
'Table'[OfferStatus] = "Under Investigation"
),
'Table'[CUSTOMER_NAME],
", "
)

Client Names Submission to Risk =
CONCATENATEX(
FILTER(
'Table',
'Table'[SUBMISSION_TO_RISK] <> BLANK() &&
'Table'[SUBMISSION_DATE_TO_CREDIT] = BLANK() &&
'Table'[APPROVAL_DATE] = BLANK() &&
'Table'[REJECTION_DATE] = BLANK() &&
'Table'[INVESTIGATION_DATE] = BLANK()
),
'Table'[CUSTOMER_NAME],
", "
)

Client Names Submission to Credit =
CONCATENATEX(
FILTER(
'Table',
'Table'[SUBMISSION_DATE_TO_CREDIT] <> BLANK() &&
'Table'[APPROVAL_DATE] = BLANK() &&
'Table'[REJECTION_DATE] = BLANK() &&
'Table'[INVESTIGATION_DATE] = BLANK()
),
'Table'[CUSTOMER_NAME],
", "
)

Client Names Approval =
CONCATENATEX(
FILTER(
'Table',
'Table'[APPROVAL_DATE] <> BLANK()
),
'Table'[CUSTOMER_NAME],
", "
)

BBF

Re: Generic date

Romani — Tue, 23 Jul 2024 08:07:04 GMT

Totally wrong
i think you need to read again what i want to give me the desired output

Re: Generic date

Romani — Wed, 24 Jul 2024 08:44:34 GMT

you didnt get my point of view
what i need to see the last date of the offer and count it in this date
The DAX measure Under Investigation counts the number of customers whose SUBMISSION_TO_RISK date is blank (meaning they are under investigation)

Under Investigation = CALCULATE(COUNT('Table'[CUSTOMER_NAME]),FILTER(ALL('Table'),[OFFERS_DATE]>=MIN('Date'[Date])&&[OFFERS_DATE]<=MAX('Date'[Date])&&[SUBMISSION_TO_RISK]=BLANK()))

Summary

Total Offers: 5
Under Investigation: 1 (Client 11)

Submission to Risk Phase

Clients whose offers ended in this phase:
- Client 32
- Client 44

Submission to Credit Phase

Clients whose offers ended in this phase:
- Client 85

Approval Phase

Clients whose offers ended in this phase:
- Client 49
  So based above info when iam chossing this period give me this result
  did you get my point ?

Re: Generic date

Romani — Sun, 28 Jul 2024 08:59:23 GMT

Anonymous ???