Forum Discussion

cinderelly678's avatar
cinderelly678
Frequent Visitor
3 years ago
Solved

Calculated Column Help Marking Consecutive Identical Values

I am having some trouble with writing DAX to mark stale incoming files. In my source, I have the columns DataLakeSourceFileAndModifiedDate, FileName, FileModifiedDate, and FileSizeBytes.

 

I want to make a new column in DAX called Stale File Flag, where the criteria is that for each FileName, if FileSizeBytes has the same value for three consecutive days, then I want to flag the 4th day's identical record as stale data. 

 

Could anyone help me out with the DAX to accomplish this? For brevity's sake I removed records from 5/1 ~ 6/25, but in my real data these rows exist with the identical FileSizeBytes. Thank you in advance for your help.

 

Here is my desired output 

 

Here is my sample data

FullFilePath	FileName	FileModifiedDate	FileSizeBytes
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/10/2023 4:31:35 PM	11592004
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/11/2023 4:31:22 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/12/2023 4:31:10 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/13/2023 4:31:40 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/14/2023 4:34:05 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/15/2023 4:31:35 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/16/2023 4:31:40 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/17/2023 4:31:33 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/18/2023 4:31:58 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/19/2023 4:31:23 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/20/2023 4:31:43 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/21/2023 4:31:12 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/22/2023 4:31:49 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/23/2023 4:32:06 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/24/2023 4:31:21 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/25/2023 4:32:07 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/26/2023 4:31:47 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/27/2023 4:31:23 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/28/2023 4:32:25 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/29/2023 4:32:37 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/30/2023 4:31:39 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/26/2023 4:31:44 PM	11636621
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/27/2023 4:31:18 PM	11574498
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/28/2023 4:33:11 PM	11618554
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/29/2023 4:32:13 PM	11598289



Need Help
Tutorial Requests

5 Replies

    • cinderelly678's avatar
      cinderelly678
      Frequent Visitor
      3 years ago

      ryan_mayu Thank you very much, this is extremely helpful. I should have specified in my sample data, I have cases where the data differs by FileName. How could the calc columns be re-written to group by FileName?

       

      Updated Sample Data

       

      FullFilePath	FileName	FileModifiedDate	FileSizeBytes	Stale Data Flag
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	11/21/2022 4:31:21 PM	5145425	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	11/22/2022 4:31:34 PM	5408281	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	11/23/2022 4:31:25 PM	5355350	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	11/27/2022 4:31:48 PM	5341969	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	11/28/2022 4:31:35 PM	5341969	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	11/29/2022 4:31:34 PM	5341969	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	11/30/2022 4:31:34 PM	5341969	Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	12/1/2022 4:31:43 PM	5341969	Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	12/2/2022 4:31:33 PM	5341969	Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	12/3/2022 4:31:29 PM	5341969	Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	12/4/2022 4:31:37 PM	5341969	Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	6/25/2023 4:32:21 PM	5282110	Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	6/26/2023 4:32:12 PM	5282110	Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	6/27/2023 4:31:42 PM	5408530	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/GROUPS.CSV	GROUPS.CSV	6/28/2023 4:33:42 PM	5431287	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	11/22/2022 4:31:16 PM	12096395	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	11/23/2022 4:31:05 PM	12106921	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	11/27/2022 4:31:24 PM	12125228	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	11/28/2022 4:31:11 PM	12125228	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	11/29/2022 4:31:14 PM	12125228	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	11/30/2022 4:31:13 PM	12125228	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	12/1/2022 4:31:22 PM	12125228	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	12/2/2022 4:31:15 PM	12125228	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	12/3/2022 4:31:09 PM	12125228	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	12/4/2022 4:31:15 PM	12125228	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	2/14/2023 4:31:32 PM	12125228	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	2/15/2023 4:31:38 PM	12125228	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	2/16/2023 4:31:30 PM	12182748	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	2/17/2023 4:31:15 PM	12170777	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	2/18/2023 4:31:14 PM	12178227	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/10/2023 4:31:35 PM	11592004	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/11/2023 4:31:22 PM	11636621	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/12/2023 4:31:10 PM	11636621	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/13/2023 4:31:40 PM	11636621	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/14/2023 4:34:05 PM	11636621	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/15/2023 4:31:35 PM	11636621	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/16/2023 4:31:40 PM	11636621	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	4/17/2023 4:31:33 PM	11636621	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/24/2023 4:31:57 PM	11636621	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/25/2023 4:31:55 PM	11636621	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/26/2023 4:31:44 PM	11636621	Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/27/2023 4:31:18 PM	11574498	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/28/2023 4:33:11 PM	11618554	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/29/2023 4:32:13 PM	11598289	Not Stale
DBFS:/MNT/ACTIVEDIRECTORY/USERS.CSV	USERS.CSV	6/30/2023 4:32:06 PM	11625059	Not Stale