Dax calculation start date & enddate

DAX DAX Calculations
1

Hi @EnterpriseDNA ,

Can you please help me with DAX calculations for below requirement?

image
image808×563 24.9 KB

i need to calculate month wise trainee days worked=

Column A-D is a data feed .

  • a) (Where the Trainee Start Date is before or equal to the final calendar day of the previous month AND the validated End Date is after the final calendar day of the previous month AND the validated End Date is before or equal to the final calendar day of the current month) If TRUE, divide the difference between the validated End Date and final calendar day of the previous month by the number of the days in the month

  • IF FALSE, move to the step below:

  • b) (Where the Trainee Start Date is before or equal to the final calendar day of the previous month AND the validated End Date is after the final calendar day of the current month) IF TRUE = 1

  • If FALSE, move to the step below:

  • c) (Where the Trainee Start Date is before or equal to the final calendar day of the current month AND the validated End Date is after the final calendar day of the current month) If TRUE, divide the difference between the final calendar day of the current month and the Trainee Start Date +1 by the number of the days in the month

  • If FALSE, move to step below:

  • d) (Where the Trainee Start Date is before or equal to the final calendar day of the current month AND the validated End Date is after the final calendar day of the previous month) If TRUE, divide the difference between the validated End Date and the Trainee Start Date +1 by the number of the days in the month.

  • If FALSE, leave cell empty

Please let me know if it’s not clear.

Regards,
Ravi

2

Hi @Ravi85,

Are you sure your formula is correct and returns the results on the image you shared with us ?

I’m wondering if the results column E are corrects.
Each time you have a condition on the “Start date” that needs to be before or equal to “31/12/2020” or “31/01/2021” for the column E. But for each start dates that are not in January 2021, the condition above will never be true… So I think that in Column E the cells will always be empty except for the first row.

Can you please verify your formula ore reformulate your problem if I did not understand it well ?
Or maybe you can provide the excel file so that we could see the formula ?

Best regards,
Joaly

3

Apologies please ignore the formula above the below steps are the ones im trying to replicate in DAX.

WIP:
i just tried creating custom column for snapshot date between start date and end date and joined that to dim date which gives me the trainee count for each month but not the exact value of that month(if they ended in middle of month) i.e 0.5 rather than 1.

hope it makes sense other wise please let me know

4

I’m sorry it’s not really clear for me…
If the trainee arrive in January and you want the value for February, what value are you expecting ?

5

Hi @JBocher ,

I have achieved this with by constructing variables for each scenario and got monthly trainee days value.

thanks for looking into it.

1 Like
6

Good news ! Sorry for not having been able to help you further.

Best regards,
Joaly

1 Like
7

Hi @Ravi85

Can you please also post a solution on the forum so the other members can benefit from it?

Thanks
Mudassir

8

Hi @MudassirAli ,

I have added custom column for snapshot date which is between start and enddates:

Snapshotdate= {([Start Date])…([End Date_org])}

later created Dim table: using m -query

joined snapshotdate <—>dimdate[date]

variables constructed using above steps and returned output based on above.
8-9 variables i had to construct and return the output.