DAX with calculation between start and finish dates

[gselvag]

Hi,

I need a expert advice on how to create a calculated column or measure. The challange I have is with following table.

Table 1 (fact table):

Name (Text type)

StartDate (Date/Time type)

FinishDate (Date/Time type)

Project (Text type)

Type (Text type)  example - budget, actual, forecast

Table 2 (calendar table):

Date

Month

Quarter

I need to figure out how to calculate number of days for each month that fall in between start - finish date.

Am thinking about measure, but maybe it's not the right way since I need to visualize (budget, actual) by month in a table and combination chart. Please, give me some help to get started on the right way.

Thanks for any advice.

[gselvag]

Hi Power BI,

I need your help folks!

Have SharePoint List (Task) that I need to calculate number of days based on Start and Finish date, grouped by Type (Actual, Budget and Forecast) and period (Month / Week). Here's some sample data.

SCHEDULE (FACT TABLE):

TaskName  |  StartDate  |   FinishDate   |    Resource   |    Type        |   IsActual   |  ExcIude

Task01          01.01.2018     30.05.2018         RES-01          Budget         False         True

Task02          10.02.2018     15.03.2018         RES-02          Budget         False         True

Task03          01.06.2018     01.07.2018         RES-01          Budget         False         True

Task04          16.03.2018     20.06.2018         RES-02          Budget         False         True

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 it in the Calculation as it's overlapping tasks (resource only allow one task either Actual or Forecast in date span).

1. Need to CALCULATE number of days (duration) by month (e.g. to be used in Combinations Chart / Pivot Table).

2. Need to CALCULATE number of days by month, divided by actual and forecast. Formula with a parametre/variable to set the current month.

Waiting for your assistance for this issue, Thanks!

[Greg_Deckler]

I'm thinking that measures are probably the way to go. Otherwise, you would need a column in your calendar table for each project or a column in your projects table for each month (wouldn't be tragic). Any way you can post sample data so that we can play around with a solution?

[gselvag]

Hi,

Have you been able to consider a solution for what am looking to acheive. Appreciate any tips - am currently going nowhere.

Thanks, Geir

[gselvag]

Thank 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.

[v-yulgu-msft · Accepted solution]

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

[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.

[gselvag]

Have a challange with calculate days between dates and visualize them by month (timephased).

The data will be based on two tables (no relationship)

1. SCHEDULE TABLE
    Table: Tasks
    Fields: Task Name, Start Date, Finish Date, Project

2. CALENDAR TABLE
    Table: Calendar
    Fields: Date, Month, Week

My question is how can I utilize a measure formula to visualize number of days, timephased (split by e.g. month and week) by project. We need a pivot like report (crosstab) with dataset to use in combination chart where days are summarized by month (columns).

Any tip on how to resolve this would help a lot.

Thanks