Need Help

[Anonymous]

Hi all, 

I need help to write a measure comparing two date fields ('GL post date' and 'Original Invoice Date') in same table (Sales Fact). I had written following code to calculate date difference with SUMX and MAXX as well, but its giving an error. I can only create measures as I have limited access to PowerBI desktop and not allowed to create column or table. Could you please let me know, if is it possible to calculate date difference for each row with this limited access? If yes, what do I need to change in following formula? 

AGE= AVERAGEX('Sales Fact', DATEDIFF('Sales Fact'[GL Post Date], 'Sales Fact'[Original Invoice Date], DAY))

[Sahir_Maharaj]

Note that this measure will only give you the total date difference for all the rows in the 'Sales Fact' table. If you want to see the date difference for each individual row, you'll need to create a pivot table or matrix visualization and place the measure in the values section of the visualization.

[Sahir_Maharaj]

This measure uses the SUMX function to sum the date differences for each row in the 'Sales Fact' table. The VALUES function is used to extract the unique values for the 'Row' column in the 'Sales Fact' table.

[Sahir_Maharaj]

Here's an alternate approach that you can use to calculate the date difference for each row in the 'Sales Fact' table:

AGE = 
VAR DateDiff = DATEDIFF('Sales Fact'[GL Post Date], 'Sales Fact'[Original Invoice Date], DAY)
RETURN SUMX(VALUES('Sales Fact'[Row]), DateDiff)

[Sahir_Maharaj]

Hello @Anonymous,

The formula you provided will return an average of the date difference between 'GL Post Date' and 'Original Invoice Date' for all the rows in the 'Sales Fact' table.