Forum Discussion
Helper IDAX with calculation between start and finish dates
Hi gselvag,
First, create a calendar table to list unique continual dates.
Calendar date = CALENDAR(MIN('Fact Table'[StartDate]),MAX('Fact Table'[FinishDate]))Then, create a calculated table via cross join FACT table and above calendar table. And add a calculated column.
Cross Join = FILTER ( CROSSJOIN ( 'Fact Table', 'Calendar date' ), [Date] >= 'Fact Table'[StartDate] && [Date] < 'Fact Table'[FinishDate] )
Month Name = 'Cross Join'[Date].[Month]Use a Matrix visual to display data records from above table.
Best regards,
Yuliana Gu
Helper IThank you for your feedback!
Here are some sample data in data model. Possible it needs more columns to make the report with measures possible. The datasource is a SharePoint Task list that is syncronized with MS Project. The project plan table is cleaned with only relevant tasks (summary items excluded). Here's some sample data.
FACT TABLE 1: SharePoint Task list with Budget items
TaskName | StartDate | FinishDate | Resource | Type
Task01 01.01.2018 30.05.2018 RES-01 Budget
Task02 10.02.2018 15.03.2018 RES-02 Budget
Task03 01.06.2018 01.07.2018 RES-01 Budget
Task04 16.03.2018 20.06.2018 RES-02 Budget
FACT TABLE 2: SharePoint Task list with Actual / Forecast items
TaskName | StartDate | FinishDate | Resource | Type | IsActual | ExcIude
Task20 01.01.2018 15.01.2018 RES-01 Actual True False
Task21 20.02.2018 25.03.2018 RES-01 Forecast False False
Task22 01.06.2018 01.07.2018 RES-02 Actual True False
Task23 02.07.2018 15.07.2018 RES-02 Forecast False False
Task23 10.06.2018 10.07.2018 RES-02 Forecast False True
The Exclude field checked TRUE and intended to avoid miscalculation of overlapping tasks (since resource only allow one task in actual/forecast date span). Maybe not best solution, but team want to review forecast to select best fit. The IsActual field is checked TRUE when 'FACT TABLE 2'Task20 is completed as actual.
1. Need to report, calculate Days durations to be visualized by month (e.g. to be used in combinations chart / crosstab / pivot / table). Slice by Resource and Measures for Budget / Actual / Forecast Type.
2. Need to create another report with actual + forecast, depending what current reporting month so that combination chart / crosstab plot actual (e.g. January) and forecast for remaining months.
Highly appreciate the help, and need to learn more how Power BI, DAX can resolve the issue.
Microsoft EmployeeHi gselvag,
First, create a calendar table to list unique continual dates.
Calendar date = CALENDAR(MIN('Fact Table'[StartDate]),MAX('Fact Table'[FinishDate]))
Then, create a calculated table via cross join FACT table and above calendar table. And add a calculated column.
Cross Join =
FILTER (
CROSSJOIN ( 'Fact Table', 'Calendar date' ),
[Date] >= 'Fact Table'[StartDate]
&& [Date] < 'Fact Table'[FinishDate]
)
Month Name = 'Cross Join'[Date].[Month]
Use a Matrix visual to display data records from above table.
Best regards,
Yuliana Gu
- gselvag
Thank you very much for your solution. Does this enable me to calculate days as decimal? I looking to find a way to calculate days with decimals (include time) for each month. Is it the datatype or do I need to crossjoin with a data time table? I hope not. Thanks.