topic Re: Comparing and Calculating Data of two dates from a single column in DAX Commands and Tips

Comparing and Calculating Data of two dates from a single column

Anonymous — Tue, 01 Sep 2020 08:48:04 GMT

Hi All,

I am trying to Compare and Calculate Data of two dates from a single date column i.e. the Net variance and Actual Variance.

For sample I have give below the Date COlumn and Employee Name so that at chosen date from slicer, the actual and net variance should pupulate.

Can someone help me with the dax. Reference image is below.

Re: Comparing and Calculating Data of two dates from a single column

amitchandak — Tue, 01 Sep 2020 09:53:32 GMT

Anonymous , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

else

Can you share sample data and sample output in table format?

Re: Comparing and Calculating Data of two dates from a single column

Anonymous — Tue, 01 Sep 2020 10:28:49 GMT

Hi amitchandak ,

Thanks for the response.

However the shared link ise useful when we want to aggregate at a given date and calculate.

But my ask is how to compare the values at given two dates from a single column?

Refer below;

Date	Employee
1/1/2019	A
1/1/2019	B
2/1/2019	A
3/1/2019	C
4/1/2019	D
5/1/2019	E
6/1/2019	A
6/1/2019	C
6/1/2019	D
6/1/2019	E

Desired Output 1
Base Date	1/1/2019
Compare Date	6/1/2019

Net variance	Actual variance
2	1
	B

Desired Output 2
Base Date	6/1/2019
Compare Date	1/1/2019

Net variance	Actual variance
-2	3
	C, D, E

Re: Comparing and Calculating Data of two dates from a single column

Greg_Deckler — Mon, 07 Sep 2020 05:27:42 GMT

Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __Previous

Re: Comparing and Calculating Data of two dates from a single column

Anonymous — Sat, 12 Sep 2020 17:20:32 GMT

Hi Greg_Deckler ,

Thank you for the response.

Request you to please refer below table for my problem statement.

My issue is I am able to calculate the employee # between two dates by comapring the names.

But when I using Group as a filter or in the Table then the count is getting increased.

Lets take an example from Table For Employee A.

For base date 6/1/19 and compare date 1/1/19, A's count should not come in Actual Variance. But if I put filter/slicer for Group. His count is coming as he has changed the group but I am trying to ignore this.

Date	Employee	Group
1/1/2019	A	India
1/1/2019	B	USA
2/1/2019	A	USA
3/1/2019	C	UK
4/1/2019	D	Canada
5/1/2019	E	Denmark
6/1/2019	A	USA
6/1/2019	C	UK
6/1/2019	D	Canada
6/1/2019	E	Denmark
7/1/2019	G	Poland
8/1/2019	H	China
9/1/2019	A	India
10/1/2019	B	USA

Desired Output 1
Base Date	1/1/2019
Compare Date	6/1/2019

Net variance	Actual variance
2	1
	B

Desired Output 2
Base Date	6/1/2019
Compare Date	1/1/2019

Net variance	Actual variance
-2	3
	C, D, E

Re: Comparing and Calculating Data of two dates from a single column

Greg_Deckler — Sat, 12 Sep 2020 17:58:14 GMT

Anonymous Is there missing data or am I missing something? Where does the variance come into play? How are you getting your expected output? I mean the logic of it? If you are saying, give me everybody that is in this first group that is not in this second group then you need to use EXCEPT.

Re: Comparing and Calculating Data of two dates from a single column

Anonymous — Sat, 12 Sep 2020 18:06:51 GMT

Hi Greg_Deckler ,

I used calculatetable to get the values for each dates (two dates: min and max).

Then I have counted the rows of the tables by comparing the values using filter condition.

This is giving me the desired output however when I am using the filter/slicer I am facing the error in count (as I have given the example of employee A).

Re: Comparing and Calculating Data of two dates from a single column

Greg_Deckler — Sun, 13 Sep 2020 16:32:56 GMT

Anonymous I need to see your calculation formulas. It is very likely that the proble lies, for examply if you are using something like ALL when calculating the minimum or the maximum. That can mess with the context from the slicer.

Re: Comparing and Calculating Data of two dates from a single column

Anonymous — Mon, 14 Sep 2020 06:34:37 GMT

Hi Greg_Deckler

Below is the DAX used;

Actaul variance =

var base = CALCULATETABLE(

DISTINCT('Table'[Employee]),

FILTER(

'Table',

'Table'[Date]=SELECTEDVALUE('base_table'[Date])

)

var compair = CALCULATETABLE(

DISTINCT('Table'[Employee]),

FILTER(

'Table',

'Table'[Date]=SELECTEDVALUE('Compair_Table'[Date])

)

var result = COUNTROWS(

FILTER(

compair,NOT([Employee] in base)

)

return if(

ISBLANK(result),

result)