cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User I
Super User I

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
Super User I
Super User I

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors