Comparing and Calculating Data of two dates from a single column

[rockykb]

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.

[amitchandak]

@rockykb , 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?

[rockykb]

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

[Greg_Deckler]

@rockykb 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/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
  __Current - __Previous

[rockykb]

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

[Greg_Deckler]

@rockykb 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.

[rockykb]

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

[Greg_Deckler]

@rockykb 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.

[rockykb]

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

0,

result)