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
PowerBIAllStars
Regular Visitor

Finding a Date variance from 2 tables

Hi I am trying to find a date difference for milestones reporting over a number of periods. Variance has to be negative if slipped bewtween the periods. The dates are indifferent tables, the row has a common Milestone ID. I have a linked the tables as below to a DAte Table so I can somehow catergorise Year/Month etc. When I try to use DateDiff I add New Column to the Table and try but I get the below error message:

 

PowerBIAllStars_1-1695309980995.png

Below is a extract of the model view.

 

PowerBIAllStars_0-1695309896485.png

 

Please help! 

 

4 REPLIES 4
PowerBIAllStars
Regular Visitor

Data Table as below, its much larger, what I would like to do is link the Forecast date per period to the "YearPeriod" data so I can filter on a particular "YearPeriod". I have linked the Baseline table to the date Table so I can show the baseline "YearPeriod", but how can I show the Forecast "YearPeriod" to multiple different coumns? 

 

Thank you. 

 

DateDateSKPeriodShortFinYearNumYearPeriodYearNum
23/06/202420240623424252425_P42024
24/06/202420240624424252425_P42024
25/06/202420240625424252425_P42024
26/06/202420240626424252425_P42024
27/06/202420240627424252425_P42024
28/06/202420240628424252425_P42024
29/06/202420240629424252425_P42024
30/06/202420240630424252425_P42024
01/07/202420240701424252425_P42024
02/07/202420240702424252425_P42024
03/07/202420240703424252425_P42024
04/07/202420240704424252425_P42024
05/07/202420240705424252425_P42024
06/07/202420240706424252425_P42024
07/07/202420240707424252425_P42024
PowerBIAllStars
Regular Visitor

Period 3 data

IDMilestone DesMS Tier23-24 P03
101MS101104/08/2024
102MS102212/08/2024
103MS103316/08/2024
104MS104115/08/2024
105MS105229/08/2024
106MS106314/09/2024
107MS107102/08/2025
108MS108211/08/2025
109MS109312/08/2025
110MS110113/08/2025
111MS111215/08/2025
112MS112312/09/2025
PowerBIAllStars
Regular Visitor

Baseline Data (at the begining of the project:

IDMilestone DesMS TierBLPMStatus
101MS101112/07/2024JiyaComplete
102MS102226/07/2024Jiya

Complete

103MS103329/07/2024JiyaIn Progress
104MS104101/08/2024HarryIn Progress
105MS105215/08/2024JohnIn Progress
106MS106328/08/2024JiyaIn Progress
107MS107115/07/2025HarryIn Progress
108MS108229/07/2025JohnNot Started
109MS109301/08/2025JohnNot Started
110MS110104/08/2025JiyaNot Started
111MS111218/08/2025HarryNot Started
112MS112331/08/2025HarryNot Started

 

Period 2 data

 

IDMilestone DesMS Tier23-24 P02
101MS101109/08/2024
102MS102216/08/2024
103MS103319/08/2024
104MS104120/08/2024
105MS105230/08/2024
106MS106319/09/2024
107MS107112/08/2025
108MS108219/08/2025
109MS109322/08/2025
110MS110123/08/2025
111MS111202/09/2025
112MS112322/09/2025
lbendlin
Super User
Super User

You'll have to work on your data model.  Do not join fact tables like this.  (Rather append them).

 

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

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