Date Table & Data Model question

[abecks44]

Hey all,

First time post, sorry if my explaination isn't amazing as I've just been learning PowerBI for the past month or two. I'm currently setting up dashboards for my company for invoice forecasting and user performance, but I'm running into an annoying issue regarding ambiguous paths and my date table.

First off, because I'm new to data modeling I'm not 100% sure if I have 1 or 2 fact tables. I know for sure that my timesheets file is a fact table, it has all of my team's timesheet data and shows what client, project, deliverable etc.. every member clocks time to every day of the year.

The other important thing for us is our deliverables. Each deliverable has its own invoice forecast date, actual forecast date, KPIs, etc..

I want to be able to tell how much time each user clocks to each deliverable but I also need to know when the revenue for each deliverable is forecast for. I've put together the data model in the attached snip, but am unable to have the date table inform both the timesheets and the deliverables simultaneously without getting an ambiguous paths error.

More than happy to answer questions to get this resolved. Basically half my visuals work and half my visuals break when on of the circled paths is active, then vise versa when switching the active paths.

[MNedix]

Heya,

When calculating the measures using the inactive relationship use the USERELATIONSHIP function. See the example below (where 'Date'[Date] active relationship is with a different column in the same table and 'Date'[Date] is in an inactive relationship with MTD[Date] which is the column I am interested in in this calculation.

MTD = 
    CALCULATE (
        SELECTEDMEASURE (),
        DATESMTD ( 'Date'[Date] ),
        USERELATIONSHIP ( 'Date'[Date], MTD[Date] )

[abecks44]

Hey! Ok so I'm not super familiar with measures yet. Would you be able to translate that generic solution into one using my datasets & columns? For reference these are the relationships I currently have from the original snip:

• Active: 2024-2025 Dates Table, column = Date -> Timesheets Table, column = Date
• Inactive: 2024-2025 Dates Table, column = Date -> Unique Deliverables Table, column = Invoice Forecast

Not sure if it's relevant or not, but for the invoice forecast date of the deliverables, I only care about the month it's forecast in. The actual day of the month is irrelevant.

[MNedix · Accepted solution]

What do you want to measure for each table? Let's assume we use the following columns for some measures:
- Table = Timesheets, Column = Hours Actual

- Table = Unique Deliverables, Column = Cost/HHP

The corresponding measures will be:

Hours = SUM('Timesheets'[Hours Actual])

Cost = CALCULATE(SUM(Cost/HHP), USERELATIONSHIP('2024-2025 Dates'[Date],'Unique Deliverables'[Invoice Forecast])

For the first measure you don't need to do much since it has an active relationship with the Date table.

And you should be able to plot in a chart the 2nd measure using the measure as Y and '2024-2025 Dates Table'[Date] for X axis.

[abecks44]

Ok! So believe it or not this was actually the first time I've written/used a measure so far in PowerBI and it looks like it is in fact showing the correct numbers in my test visualization so thank you for that! I guess maybe a newbie follow up question: I'm seeing that this measure was added to the 2024-2025 Dates dropdown in my Data, is this due to just me having that selected at the time of writing the measure or is that an artifact of how the measure was written? Snip attached but I've removed all of the visualization data for security/privacy reasons