CODEWITHSUNDEEP
powerbidax
Kristian Petersen
Kristian Petersen

Reputation: 1

DAX function to check the amount of dates that is greater than another set of dates in 2 different columns

I'm currently doing an internship where I have been asked to make a few visuals in Power BI

I've searched around, tried a couple of things. But the truth is I am very much a beginner at coding and functions in general. Only had basic courses of different languages during my education and to be fair, it's a bit outside my scope of work.

So I have 2 columns I need to compare in order to find out how many dates in column 2 that is greater than the dates in column 1

So I'm imagining something like:

Measure = IF[(Investments(Expected closure)]<[(Investments(Actualclosure)]

Basically I want an overview of how many investments have a later closure date than expected.

Next thing would possibly be to create a boxplot showing the distribution (by how far we are off).

I know this is very basic, and possibly not formulated in the best way possible, please let me know if you need any more information.

Thanks in advance

Upvotes: 0

Views: 3548

Answers (2)

rohit10
rohit10

Reputation: 11

You can use a calculated column as a flag to identify if actual date > expected date and then count the flag.

Flag = IF('Table'[Act] > 'Table'[Exp], 1, 0)

Hope this helps. Thanks.

enter image description here

Upvotes: 1

JoPapou13
JoPapou13

Reputation: 773

Welcome to the community. Be sure to read to read this for posting questions.

For your questions, you can use the following code. You are filter the table with your logical expression with FILTER and then you count the lines of a column with COUNTA.

Measure = CALCULATE(COUNTA('Investments'[Actualclosure]),FILTER(ALL('Investments'),'Investments'[Actualclosure]>'Investments'[Expected closure]))

Hope this solves your problem.

Upvotes: 0

Related Questions