Count of Days in a Measure instead of Calculated Column

[Anonymous]

Number of Days in Care = IF(ISBLANK([END_DATE]),DATEDIFF([START_DATE],TODAY(),DAY),DATEDIFF([START_DATE],[END_DATE],DAY))

Please can someone assist me with the above?

I need the same logic above to be put translated into a measure, as the calculated column is significantly impacting performance in Power BI.

I have a table with the columns 'PERSON_ID, START_DATE, END_DATE'. I need to have a measure that gives me the following:

PERSON_ID with START_DATE and END_DATE, therefore give me number of days

PERSON_ID with START_DATE and no END_DATE, therefore give me number of days from START_DATE to today.

Thanks,

Dan.

[TomMartens]

Hey @Anonymous ,

you should consider this DAX for a calculated columns as this is using variables to avoid multiple column evaluations inside the IF

column name = 
var _today = TODAY()
var _startdate = [START_DATE]
var _enddate = [END_DATE]
var __enddate = IF(NOT(ISBLANK(_enddate)) , _enddate , _today)
return
DATEDIFF(_startdate,__enddate,DAY)

As the Start- and Enddate can be considered being properties of the PERSON_ID the measure will look similar:

measure name =
SUMX(
	VALUES('tablename'[PERSON_ID])
	,var _today = TODAY()
	var _startdate = [START_DATE]
	var _enddate = [END_DATE]
	var __enddate = IF(NOT(ISBLANK(_enddate)) , _enddate , _today)
	return
	DATEDIFF(_startdate,__enddate,DAY)
)

Hopefully, this provides what you are looking for.

Regards,

Tom

[Anonymous]

Hi @TomMartens ,

Thanks for this but unfortunately the calculated column didn't work, it returned the same value for every single PERSON_ID. I couldn't get the measure to work at all due to syntax. I believe you understand my issue but it seems I have not been very clear, given other users' comments. To clarify:

I have a calculated column with this current DAX:

Number of Days = IF(ISBLANK([END_DATE]),DATEDIFF([START_DATE],TODAY(),DAY),DATEDIFF([START_DATE],[END_DATE],DAY))

It produces this current data table (which is 'PersonTable') :

PERSON_ID	START_DATE	END_DATE	Number of Days (calculated column)
1	23 July 2004	30 July 2004	7
2	10 April 2004	10 April 2005	365
3	10 August 2020	(blank)	2

I have a table visualisation in my Power BI report page with PERSON_ID and Number of days as values. I have a filter so that I only have with PERSON_ID with '(blank)' END_DATE in the table:

PERSON_ID	Number of Days (Calculated column)
3	2

My calculated column works, but it is going through every single row and is causing the visualisation to take a very long time to load.

I hope this is clear and thanks everyone for your inputs @amitchandak @Greg_Deckler 

[v-deddai1-msft]

Hi @Anonymous ,

Would you please refer to the measure below：

Measure = IF(ISBLANK(MAX('Table'[END_DATE])),DATEDIFF(MAX('Table'[START_DATE]),TODAY(),DAY),DATEDIFF(MAX('Table'[START_DATE]),MAX('Table'[END_DATE]),DAY))

 

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

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft ,

the measure doesn't work as it returns the same number of days for every PERSON_ID, eg:

PERSON_ID	Number of Days Measure
1	100
2	100
3	100
4	100

[TomMartens · Accepted solution]

Hey @Anonymous ,

based on my sample data:

here is the correct measure, be aware of the slightly changed first parameter of the SUMX function:

measure name = 
SUMX(
	VALUES('table')
	,var _today = TODAY()
	var _startdate = [START_DATE]
	var _enddate = [END_DATE]
	var __enddate = IF(NOT(ISBLANK(_enddate)) , _enddate , _today)
	return
	DATEDIFF(_startdate,__enddate,DAY)
)

my table visual:

I doubt that the measure approach will speed up the loadig of the visual.

A calculated will evalutated just once, this happens when the data model is loaded. If we'are talking about Power BI Desktop, this happens when you open the report, when you refresh the underlying data, or when you change a calculatio.

Indeed, a measure will be only evelauted when it's needed, so basically using a measure should be used instead of a calculated column, as the overall memory footpring will be smaller using a measure.

But, in this special case, if you are aiming for speeding up the load time of a specific visual, my assumption is that, the calculated column will be faster than the measure, as the value of the calculated columns just has to be visualized where the measure has to be evaluated and then visualized.

Regards,

Tom

[Anonymous]

@TomMartens  OK thanks for the advice, I will leave as a calculated column based on your input! 

FYI the measure works too 😎

[TomMartens]

Hey @Anonymous ,

prepare a pbix with sample data, upload the pibix to onedrive or dropbox and share the link (make sure that start date and end date date have a date or datetime data type). If you are using an xlsx to create the sample data and share the xlsx as well.

Hmm, wondering what is going as you can see, the DAX statement creates different values, based on admittedly small sample data:

Regards,

Tom

[Anonymous]

@TomMartens 

thanks so much for your help. Today I have tried the calculated column and for some reason it works absolutely fine now, thanks! This will certainly help performance issues and is a great temporary fix. Now I would like to progress to a measure, as I believe this will further decrease the performance time to load the visualisation.

The measure however has not been succesful. Note I had to edit 'var _startdate' and 'var _enddate' as they needed to have a MAX/MIN function. Please see the screenshot below:

As you can see, it will also not take '_enddate' and '__endate'

I hope this means we no longer need the sample data and pbix file, but please let me know if this is still the case.

Thanks very much Tom!

[amitchandak]

@Anonymous , Not very clear.

refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Greg_Deckler]

@Anonymous So generally you convert a column to a measure by adding aggregatins like MAX/MIN, etc. to column references. That is very general and depends on context. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.