Analysing data before and after a mileston date.

[Anonymous]

Hi, I am new here.

I have tried to look at the forums to initially solve my problem, but I am struggling to find a clear solution.

I have one table.

Job No.	Job Start
98	01 Mar 19
99	01 Aug 19

And my fact table

Job No.	Task	Date	Hours
99	Support	14 April 19	5
99	Accounting	26 Aug 19	2

I would like to develop a profile to analyse jobs based on months before and after [job](job start)

Job No.	Task	Month 1	Month 2	Month 3	Month 4
98	Set-up	5 hours	10 hours	15	10
99	Support	0	0	2	3
99	Accounting	0	10	5	5

In my little head - 

1. Modify [fact table](date) to month, or 1st of the month

2. Calculated column on fact table = [fact table](date) - [job](job start).

eg.   26th August - Modified to 01 Aug -

01 Aug - 01 Aug = Month 0

Is this how you would structure and solve the problem? 

Do  I create a whole new table with the new data?

If that is the right way, how do I go about it.

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can refer to the following steps if they are suitable for your requirements.

Steps:

1. Use 'job No' as relationship key to link two tables.

2. Create a Calendar table with whole date records.

3. Create a matrix visual with table[job No], fact[Task] as row, calendar[Year],calendar[Month] as column.

4. Write a measure to calculate based on matrix row, column and use on the above matrix visual value field.

Measure = 
VAR currdate =
    MAX ( 'Calendar'[Date] )
VAR jobstart =
    CALCULATE ( MAX ( 'Table'[Job Start] ), VALUES ( 'Table'[Job No.] ) )
VAR startDate =
    CALCULATE (
        MAX ( 'Fact'[Date] ),
        VALUES ( 'Fact'[Job No.] ),
        VALUES ( 'Fact'[Task] )
    )
VAR workhour =
    CALCULATE (
        MAX ( 'Fact'[Hours] ),
        FILTER ( ALLSELECTED ( 'Fact' ), RELATED ( 'Table'[Job Start] ) <= currdate ),
        VALUES ( 'Table'[Job No.] ),
        VALUES ( 'Fact'[Task] )
    )
VAR duartion =
    DATEDIFF (
        DATE ( YEAR ( MAX ( jobstart, startDate ) ), MONTH ( MAX ( jobstart, startDate ) ), 1 ),
        DATE ( YEAR ( currdate ), MONTH ( currdate ), 1 ),
        MONTH
    )
RETURN
    IF ( workhour <> BLANK (), workhour * duartion, 0 )

Regards,

Xiaoxin Sheng

[Anonymous]

That is comprehensive!

On some sample data it is looking good thanks. I will give it a trial on the main info.

Thanks!

[kentyler]

the standard way to deal with dates in power bi is to add a "calendar" table, a dimension table for dates.

(you can find a lot of samples by googling "calendar table")

here's what your schema looks like with the calendar table added

the calendar table will let you filter on months without having to do date math to find start and end dates.
once you have the calendar table you can look at dax functions like PREVIOUSMONTH and find various ways of looking forwards and backwards in time.

[Anonymous]

Hi @kentyler 

I already have a date table, but don't understand how that will help.

The transform is dependent on start date in the job table and then translating that to month 1,2,3, so that I can compare different projects at different start times. Ie the dates month 1,2, are dynamic across each project.

I'm probably missing something here, and I'm more of a civilian dev rather than professional.