cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SynetonBI Frequent Visitor
Frequent Visitor

Representing a timeline with from date and until date records

Dear Sirs,

 

I have a table like this:

 

Employee IDWage FromWage UntilWage
0012018-01-012018-08-051000
0012018-08-062019-01-151200
0012019-01-16 1300

 

This is an example of one employee and his history of wages.

What i want is a line chart of every day and check what wage this employee has on that moment.

So for employee 001 it means that when the time frame or date filters are between 2018-01-01 and 2018-08-05, the wage is 1000.

 

Kind regards.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Representing a timeline with from date and until date records

use a calcluated table 

 

first go into the data view 

select the modeling tab 

click new table 

 

in the formula bar enter the dax below and change the required table names

 

Table 2 =
SELECTCOLUMNS(
GENERATE(
'Table1',
FILTER(
CALENDAR(MIN('Table1'[Wage From]),TODAY())
,[Date]>=[Wage From] && [Date] <= IF(NOT IsBlank([Wage Until]),[Wage Until],TODAY()
)
)
),"ID",[Employee ID],"wDate",[Date],"wage",Table1[Wage])
 
this will genarate a table for each date inbetween the start and end date on enach line of your table with with the wage and employee id 
line data.png
you can then use this table to craete your line graph
 
line.png
1 REPLY 1

Re: Representing a timeline with from date and until date records

use a calcluated table 

 

first go into the data view 

select the modeling tab 

click new table 

 

in the formula bar enter the dax below and change the required table names

 

Table 2 =
SELECTCOLUMNS(
GENERATE(
'Table1',
FILTER(
CALENDAR(MIN('Table1'[Wage From]),TODAY())
,[Date]>=[Wage From] && [Date] <= IF(NOT IsBlank([Wage Until]),[Wage Until],TODAY()
)
)
),"ID",[Employee ID],"wDate",[Date],"wage",Table1[Wage])
 
this will genarate a table for each date inbetween the start and end date on enach line of your table with with the wage and employee id 
line data.png
you can then use this table to craete your line graph
 
line.png