Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
AnthonyTilley
Solution Sage
Solution Sage

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
AnthonyTilley
Solution Sage
Solution Sage

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!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.