Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there, I would like to request some help on this.....
I have a need to use the Timeline slicer which usually has a Start Date and an End Date
Imagine i populate dataset of historical data based on multiple excel file,
As Such
File: | 28/03/2018 | File: | 04/04/2018 | File: | 11/04/2018 | ||
ID | Name | ID | Name | ID | Name | ||
00001 | Bryan | 00001 | Bryan | 00001 | Bryan | ||
00002 | John | 00002 | John | 00002 | John | ||
00003 | Mac | 00003 | Mac | 00003 | Mac | ||
00004 | Annie | 00004 | Annie | 00005 | Henry | ||
00005 | Henry | 00007 | Paul | ||||
00006 | John | 00008 | Jack | ||||
00007 | Paul | 00009 | Liam | ||||
00008 | Jack | 00010 | Hank | ||||
00009 | Liam |
It Then Gets combined into one Combined Dataset for which the dashboard can pull data for historical purposes like follow.
Combined File | ||
Date | ID | Name |
28/03/2018 | 00001 | Bryan |
28/03/2018 | 00002 | John |
28/03/2018 | 00003 | Mac |
28/03/2018 | 00004 | Annie |
04/04/2018 | 00001 | Bryan |
04/04/2018 | 00002 | John |
04/04/2018 | 00003 | Mac |
04/04/2018 | 00004 | Annie |
04/04/2018 | 00005 | Henry |
04/04/2018 | 00006 | John |
04/04/2018 | 00007 | Paul |
04/04/2018 | 00008 | Jack |
04/04/2018 | 00009 | Liam |
11/04/2018 | 00001 | Bryan |
11/04/2018 | 00002 | John |
11/04/2018 | 00003 | Mac |
11/04/2018 | 00005 | Henry |
11/04/2018 | 00007 | Paul |
11/04/2018 | 00008 | Jack |
11/04/2018 | 00009 | Liam |
11/04/2018 | 00010 | Hank |
What i need to do is Using that Timeline Slider, create a list of the ID's that were Added between Start date and End date
and those that have been removed from Start Date to End Date.
Results should be as such.
Start Date: 28/03/2018 End Date: 04/04/2018
Added
Date Added | ID | Name |
04/04/2018 | 00005 | Henry |
04/04/2018 | 00006 | John |
04/04/2018 | 00007 | Paul |
04/04/2018 | 00008 | Jack |
04/04/2018 | 00009 | Liam |
Removed
Or this Example
Start Date: 28/03/2018 End Date: 11/04/2018
Added
Date Added | ID | Name |
04/04/2018 | 00005 | Henry |
04/04/2018 | 00006 | John |
04/04/2018 | 00007 | Paul |
04/04/2018 | 00008 | Jack |
04/04/2018 | 00009 | Liam |
11/04/2018 | 00010 | Hank |
Removed
Date Removed | ID | Name |
11/04/2018 | 00004 | Annie |
11/04/2018 | 00006 | John |
Notice that Date Added is the date of first occurence within the time selected in the Timescale Slicer
and the Date Removed is the date between the date of first occurence and the last date selected in the Timescale Slicer.
Would appreciate if someone can give some advise on how to do this.
@Bastille92,
To check added rows, you can create the following columns in the combined table.
PreID = CALCULATE(FIRSTNONBLANK(Table1[ID],1),FILTER(Table1,Table1[ID]=EARLIER(Table1[ID]) && Table1[Date]<EARLIER(Table1[Date])))
checkaddrow = IF(ISBLANK(Table1[PreID]) && Table1[Date]>MIN(Table1[Date]),1,0)
Then drag checkaddrow column to visual level filter and set its value to 1.
To check removed rows, the method that I can think of is to create new table listing missing rows between tables using Except() function, there is a similar thread for your reference.
Regards,
Lydia
So right now the scenario i am having is this.
Table 1 | Table 2 | |||||
ID | Date | ID | Min Date | Max Date | ||
000001 | 15/01/2018 | 000001 | 15/01/2018 | 19/01/2018 | ||
000001 | 19/01/2018 | 000002 | 08/01/2018 | 22/03/2018 | ||
000002 | 22/02/2018 | 000003 | 18/01/2018 | 01/02/2018 | ||
000002 | 08/01/2018 | |||||
000002 | 11/01/2018 | |||||
000002 | 22/03/2018 | |||||
000003 | 18/01/2018 | |||||
000003 | 22/01/2018 | |||||
000003 | 01/02/2018 |
I need a Fomula for Calculated Columns "Min Date" and "Max Date" by looking up table 2 ID to seach the min and max date by looking up to Table 1 ID.
Okay Now i managed something like this.
Where
Add Date = Calculate(MIN('Snap Historical'[Snap Date]),FILTER(ALL('Snap Historical'[C-Number]),'Snap Historical'[C-Number]='Table'[C-Number]))
Removed Date = IF(Calculate(MAX('Snap Historical'[Snap Date]),FILTER(ALL('Snap Historical'[C-Number]),'Snap Historical'[C-Number]='Table'[C-Number]))=MAX('Snap Historical'[Snap Date]),Blank(),Calculate(MAX('Snap Historical'[Snap Date]),FILTER(ALL('Snap Historical'[C-Number]),'Snap Historical'[C-Number]='Table'[C-Number])))
However, now i need assistance to modify the "Add Date" so that the Returned "Add Date" will Always be getting the Minimum Date that is Larger than " Removed Date"
Add Date = Calculate(MIN('Snap Historical'[Snap Date]),FILTER(ALL('Snap Historical'[C-Number]),'Snap Historical'[C-Number]='Table'[C-Number]),FILTER(ALL('Snap Historical'[Snap Date]),'Snap Historical'[Snap Date]>='Table'[Removed Date]))
Is this right??
Can i be using >= or it needs to be in a specific format like on Excel where its >=&'Table'[Removed Date]
Bump,
still awiting on a resoponse to see if the formula for hte previous post for the >= works or it has to be >=&
Regards
@Bastille92,
What are the tables(Snap Historical, Table) do you refer to in your DAX? And what is the expected result based on the new Table 1 and Table 2?
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |