topic Re: Create date difference value column ? in DAX Commands and Tips

Create date difference value column ?

Anonymous — Sun, 16 Jan 2022 18:01:41 GMT

Hello,

I want to create a column like created column( current date - last date by country) in power bi.

I have country/Region, date and death column.

How do I create created column?

Country/Region	Date	Deaths	Created column
Brazil	24-Apr-21	389,492	3,076
Brazil	23-Apr-21	386,416	2,914
Brazil	22-Apr-21	383,502	2,027
Brazil	21-Apr-21	381,475	3,472
Brazil	20-Apr-21	378,003	0
Argentina	24-Apr-21	432,312	90
Argentina	23-Apr-21	432,222	679
Argentina	22-Apr-21	431,543	309
Argentina	21-Apr-21	431,234	1,000
Argentina	20-Apr-21	430,234	0

thanks in advance

Re: Create date difference value column ?

ValtteriN — Sun, 16 Jan 2022 18:24:56 GMT

Hi,

Here is one way to this:

EarlierDatediff =

var _country = 'Table (5)'[Country/Region]

var _edate = CALCULATE(MAX('Table (5)'[Date]),FILTER('Table (5)','Table (5)'[Date] <EARLIER('Table (5)'[Date])),'Table (5)'[Country/Region]=_country)

var _datediff = DATEDIFF('Table (5)'[Date],_edate,DAY)

return

_datediff

End result (I modified 1 date to demo -2 in Argentina):

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Create date difference value column ?

AlexisOlson — Sun, 16 Jan 2022 18:31:11 GMT

If you want to do this in the query editor, have a look at my previous solution here:
https://community.powerbi.com/t5/Power-Query/Optimize-performance-at-Un-Cumulate-calculation/m-p/2194983#M64867

You may want to review this simpler case explained with more detail first:
https://community.powerbi.com/t5/Desktop/Calculating-Daily-values-from-Cumulative-Total/m-p/2198969

Re: Create date difference value column ?

Anonymous — Sun, 16 Jan 2022 18:35:46 GMT

Hello,

Your solution might not work as we have to take the country into the account as well.

thanks

Re: Create date difference value column ?

Anonymous — Sun, 16 Jan 2022 18:38:10 GMT

Hello,

Sorry, let me rephrase the problem.

I have to create a column with current date value - previous date value by country.

your solution is only giving the datediff.

thanks

Re: Create date difference value column ?

AlexisOlson — Sun, 16 Jan 2022 18:40:20 GMT

The first one I linked to does handle this. The second one is the simpler example that does not.

Re: Create date difference value column ?

Anonymous — Sun, 16 Jan 2022 18:41:33 GMT

Sure thanks, will take a look.

Re: Create date difference value column ?

VahidDM — Sun, 16 Jan 2022 23:25:48 GMT

Hi Anonymous

Try this code to add a new column :

Column = VAR _CD = 'Table'[Date] VAR _YD = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] < _CD && 'Table'[Country/Region] = EARLIER ( 'Table'[Country/Region] ) ) ) VAR _YDV = CALCULATE ( MAX ( 'Table'[Deaths] ), FILTER ( ALL ( 'Table' ), 'Table'[Date] = _YD && 'Table'[Country/Region] = EARLIER ( 'Table'[Country/Region] ) ) ) RETURN IF ( ISBLANK ( _YD ), 0, 'Table'[Deaths] - _YDV )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/