Dax Formula to use two dates (input from filters)

[surajbh]

The below table represents the birds eye view of data, here I am interested to calculate the status (New, updated, unchanged, deleted) on the basics of the date filters1 and datefilters2-

	Date	Primary key	Sales	status
	06.10.2021	A	5	New	Date Filters 1=	the user chose the date
	06.10.2021	B	1	New	Date Filters 2=	the user chose the date
	06.10.2021	C	6	New
	07.10.2021	A	10	updated
	07.10.2021	B	2	updated
	07.10.2021	C	6	Unchanged
	07.10.2021	D	4	New
	07.10.2021	E	5	New
	08.10.2021	B		Updated
	08.10.2021	C	1	updated
	08.10.2021	D	1	updated
	09.10.2021	F		new
	09.10.2021	G	1	New
	09.10.2021	H		New
		The Status attributes( New, Updated, Unchanged, Deleted) should be compared with respect to the date filters 1 and date filters 2 ( depending upon the date Filters1 and Date Filters2, the Status attributes should change).

Thank you very much in advance for the support. 

[v-robertq-msft · Accepted solution]

Hi, @surajbh 

According to your description, I can roughly understand your requirement, I think you can try my steps that uses two calculated tables and two measures to achieve your requirement:

This is my test data based on the sample data you posted:

1. Create two calculated table like this:

Filter1 = SUMMARIZE('Table',[Date])

Filter2 = SUMMARIZE('Table',[Date])

2. Then create two date slicers using the date columns within the two tables and set them to list mode:

3. Create two measures like this:

Flag =

var _date1=SELECTEDVALUE(Filter1[Date])

var _date2=SELECTEDVALUE(Filter2[Date])

return

IF(MAX('Table'[Date]) = _date1||MAX('Table'[Date]) = _date2,1,0)

Status =

var _date1=SELECTEDVALUE(Filter1[Date])

var _date2=SELECTEDVALUE(Filter2[Date])

var _key1=SELECTCOLUMNS(FILTER(ALL('Table'),[Date]=_date1),"1",[Primary key])

var _key2=SELECTCOLUMNS(FILTER(ALL('Table'),[Date]=_date2),"1",[Primary key])

return

IF(

    MAX('Table'[Date])=_date1,

    SWITCH(TRUE(),

    not(MAX('Table'[Primary key]) in _key2),"Deleted",

    BLANK()),

    SWITCH(TRUE(),

    not(MAX('Table'[Primary key]) in _key1),"New",

    MAX('Table'[Sales])=CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),[Primary key]=MAX('Table'[Primary key])&&[Date]=_date1)),"Unchanged",

    "Updated"))

4. Create a table chart and place it like this, then set the visual filter like this:

And you can finally get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[surajbh]

@ERD thank you for the questions-as we have 2 dates filters ( comparing like 5th of the month to 10th of the month)
if Primarykey on 5th was there but wasn't available on the 10th then it should show deleted.

Regarding the date filters- I would like to choose the two days (1 from datefilters1 and second from date filters 2) between the range of my data.
let me know if you need some more clarification. 
thank you 

[ERD]

@surajbh , so, if I understand correctly you just need a Date slicer with From (what you call Date Filters 1) and To (what you call Date Filters 2) dates:

 Can you, please, show the output table with Deleted status as well?

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[surajbh]

Hi, 
I have tried below to explain thoroughly, let me know if it is still unclear. 

the above is the database, when i use the two filters (as shown below)

Date Filters 1=	07.10.2021
Date Filters 2=	11.10.2021

Then I want to create a column- STATUS on the basics of two filters that I put manually to show like this.- 

thank you in advance for the support.

[ERD]

@surajbh ,

The logic from your resulting table from what I see is the next:

• you compare only 2 dates
• status for the first date: if value cannot be found for the next date, put Deleted. Otherwise put blank()
• status for the second date:
  • if the value has changed - update
  • if the value is the same - unchanged
  • if the value wasn't found in first date - new

Please, correct me if I'm wrong.

[ERD]

@surajbh ,

what I've got from your explanation\example:

• New - if Primary key is the earliest in table
• Updated - if value has changed for the same Primary key
• Unchanged - if Primary key met again but no changes in value
• Deleted - ?

What's with date filters? Do you need them to define the period From - To ?

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[surajbh]

Hi there, 

Deleted- if the primary key is not there for the second date