Help with the date fields created with DAX

[thanish]

Hi I have a situation like a product is rented(delivered_date) and got back(collected_date) and created_date is the invoice date.
Now I will have to find out the utilization of the product based on the month I chose to see the report. 

If a product is rented from Dec 1st 2015 to Feb 15th 215 for example and invoice is generated for each month. Suppose if I want to find the utlization for the month of January 2015 then the utilization is 100% because the product was with the client for the entire month of January 2015 so the utilization start date is Jan 1st 2015 and Utilizationend date is Jan 31st 2015.

In some cases the data present in the invoice table is not appropiate. Sometimes the Delivery date is Dec 15th 2015 and collection date is Dec 25th 2015 but Invoice might have been generated in the month of Jan 2016. Say may be Jan 25th 2016 which is wrong because the invoice will not be generated for product which were returned a month back.

So one of the engineers told us there must be a mistake in the data entered in the collection date as if the invoice is generated that means the product is still with the client.

So owing to the above scenario I had to create the util_start_date and end_date in such a format when the delivered date and collected date is before the invoice was generated(means it's wrong and the product is still with the client) then I got to replace the utilzation start date and end date to be the entire month the report is generated.

 According to the above scenario Dec 15th 2015 the product was delivered and collection date is Dec 25th 2015 and invoiced on Jan 25th 2016. So the collected date here is wrong though may be the delivered date is right. So if I generate a report for Jan it should show that my utilzation start date is Jan 1st 2016 and end date is Jan 31st 2016. I have created a date filter with created date(invoiced date) and the table for the details.

I was able to achieve utilization dates with the below formula for utilzation_start_date and utilzation_start_date_new with the only difference highlighted in red. Similary for utilization_end_date and utilization_end_date_new 

util_start_date =
IF(
CALCULATE ( MIN ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date]), ALLSELECTED ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date] ) ) - MIN('Final_Incovice_&_BP_(merged)'[Delivery Date]) >0
&&
CALCULATE ( MIN ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date]), ALLSELECTED ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date] ) ) - MAX('Final_Incovice_&_BP_(merged)'[Collection_Date_updt]) > 0,

MIN('Final_Incovice_&_BP_(merged)'[Delivery Date])

IF(CALCULATE ( MIN ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date]), ALLSELECTED ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date] ) ) - MIN('Final_Incovice_&_BP_(merged)'[Delivery Date]) > 0,

CALCULATE ( MIN ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date]), ALLSELECTED ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date] ) ),

MIN('Final_Incovice_&_BP_(merged)'[Delivery Date]) ))

util_start_date_new =
IF(
CALCULATE ( MIN ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date]), ALLSELECTED ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date] ) ) - MIN('Final_Incovice_&_BP_(merged)'[Delivery Date]) >0
&&
CALCULATE ( MIN ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date]), ALLSELECTED ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date] ) ) - MAX('Final_Incovice_&_BP_(merged)'[Collection_Date_updt]) > 0,

CALCULATE ( MIN ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date]), ALLSELECTED ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date] ) ),

IF(CALCULATE ( MIN ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date]), ALLSELECTED ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date] ) ) - MIN('Final_Incovice_&_BP_(merged)'[Delivery Date]) > 0,

CALCULATE ( MIN ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date]), ALLSELECTED ( 'Final_Incovice_&_BP_(merged)'[CreatedDate].[Date] ) ),

MIN('Final_Incovice_&_BP_(merged)'[Delivery Date])))

Now the real problem is I am getting extra rows in the utilization_start_date_new and utilization_ends_date_new compared to util_start_date & util_end_date though I have selected only the month of January in the date filter 
. Previously I filtered out the row that were blank in the util_start_date & util_end_date and displayed the report. Now since utilization_start_date_new and utilization_ends_date_new are going to be my fields of interest for the report I am unable to filter the rows that are not necessary. Could you please help me out with this ?

 

[v-shex-msft]

Hi @thanish,

Since I not clear your table structure, can you provide a pbix file with some sample data to test?

Regards,

Xiaoxin Sheng