Need Help in Scenario

[Preeti_Yadav]

Hi ,

I need help in implementing the following scenario :

I have a table where columns are  employee name ,from date ,to date 

What am trying to implement is a dax where i want to see whether that employee is billable or not for the selected timeperiod.

For example: If i select any dates between the mentioned FromDate - ToDate  from the slicer if the employee name is there between the range date it should be seen billable else not. And which date i need to take in slicer??

Thanks in advance!!

[ERD]

@Preeti_Yadav ,

You can try the measure below. Date table is not connected to the Data table in the example and is used in the slicer.

InRange = 
VAR minDate = MIN ( 'Date'[Date] )
VAR maxDate = MAX ( 'Date'[Date] )
VAR c_name = SELECTEDVALUE ( T[Name] )
VAR c_name_from = CALCULATE ( MAX ( T[From] ), T[Name] = c_name )
VAR c_name_to = CALCULATE ( MAX ( T[To] ), T[Name] = c_name )
RETURN
    IF ( minDate <= c_name_from && maxDate >= c_name_to, "InRange", "Not InRange" )

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

[Mohammad_Refaei]

Which date is the billing date? From or to?

[Preeti_Yadav]

It should between the from date to To date, So for this do we need to create one more table were all the dates are there??

[Mohammad_Refaei]

Well, by any means you should use a calendar table.

Go to Modeling, New Table

Calender =
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2021, 12, 31 ) )

//adjust the date range

Also create a new measure to evaluate the date range and apply the filter on the matrix visual.

InRange =
VAR FirstSelectedDate =
    MIN ( 'Calendar'[Date] )
VAR LastSelectedDate =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        SELECTEDVALUE ( MyTable[FromDate] ) >= FirstSelectedDate
            && SELECTEDVALUE ( MyTable[ToDate] ) <= LastSelectedDate,
        "InRange",
        "Not InRange"
    )

[Preeti_Yadav]

Hi @Mohammad_Refaei ,

Thank you for your reply, but
there is an inactive relation between one of the date ,thats why its not giving the exact output,can you give me some suggestion to resolve that.

Thanks!!

[Mohammad_Refaei]

Hi @Preeti_Yadav , I think before discussing the DAX we have to discuss the business logic.

Usually the bill is due on a certain specific day. You know that your electricity bill for example is due the 1st or 15th day of the month. The same for your credit card, phone billls... etc.

The billing due date is different from the grace period to fulfill  the payment.

So, you have to decide on the billing date (one field). In your report, you can analyze all due bills and separately you can analyze overdue or delayed payments which crossed the last collection due date (I assume it is the todate field in your case).