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
Mehanam3885
Frequent Visitor

Date Difference between 2 Columns

Hello,
I have a 2 table with
table A contains Start & End dates as of which i need to calculate the difference between them
Table B contains 2 other columns which i need to use as a filter.

The dax formula used is as below, but the result is far from right (it should give me only around 21 weeks but it is giving me 72 weeks

Mehanam3885_1-1698320927006.png

 

No.of Fabrication Weeks =
VAR SelectedActivityDescription = SELECTEDVALUE(ActivtiyLevelProgress[ActivityDescription])
VAR SelectedCustomText01 = SELECTEDVALUE(Equipment[CustomText01])
VAR TotalDuration =
    CALCULATE(
        SUMX(
            Equipment,
            IF(
                Equipment[CustomText01] = SelectedCustomText01,
                DATEDIFF(Equipment[InstallationStart], Equipment[InstallationEnd], WEEK)
            )
        ),
        FILTER(
            ActivtiyLevelProgress,
            ActivtiyLevelProgress[ActivityDescription] = SelectedActivityDescription &&
            ActivtiyLevelProgress[Progress] = 100
        )
    )
RETURN
    TotalDuration


Sample Dataset
Sample Dataset 

what did go wrong in the Dax formula ?

2 REPLIES 2
some_bih
Super User
Super User

Hi @Mehanam3885 without model, it is hard to spot what could be wrong. Still, please check FILTER part of CALCULATE (ActivtiyLevelProgress[Progress] = 100)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






The model size is big and contains alot on confidential data.
I have uploaded a sample of the Data in excel in the orginal post,kindly check if you can please.

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.