topic Re: Dax Formula to use two dates (input from filters) in DAX Commands and Tips

Dax Formula to use two dates (input from filters)

surajbh — Mon, 25 Oct 2021 14:48:01 GMT

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.

Re: Dax Formula to use two dates (input from filters)

ERD — Mon, 25 Oct 2021 20:54:39 GMT

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.

Re: Dax Formula to use two dates (input from filters)

surajbh — Tue, 26 Oct 2021 05:02:01 GMT

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

Re: Dax Formula to use two dates (input from filters)

ERD — Tue, 26 Oct 2021 07:08:24 GMT

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.

Re: Dax Formula to use two dates (input from filters)

surajbh — Tue, 26 Oct 2021 07:28:55 GMT

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.

Re: Dax Formula to use two dates (input from filters)

ERD — Tue, 26 Oct 2021 17:36:50 GMT

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.

Re: Dax Formula to use two dates (input from filters)

v-robertq-msft — Thu, 28 Oct 2021 08:56:41 GMT

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:

Create two calculated table like this:

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

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

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

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"))

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.

Re: Dax Formula to use two dates (input from filters)

surajbh — Mon, 22 Nov 2021 10:11:48 GMT

Hi there,

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