Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
thanish
Frequent Visitor

Help with the date fields created with DAX

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])))

 

Untitled.png

 

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 ?

 

Untitled1.png 

 

 

1 REPLY 1
v-shex-msft
Community Support
Community Support

Hi @thanish,

 

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

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors