Forum Discussion
Date Table & Data Model question
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.
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.
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.
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