Anyone know a DAX that will calculate a date between every single date...

[GenericUser1]

...and the date that is a month behind that single date, if you get what I mean. Basically I am trying to get a rolling month that calculates the average minutes it takes to execute a task. if you need any clafications etc let me know! Thanks

[Anonymous]

How you achieve that will depend on a number of factors. Post an example of what your data looks like and perhaps we can suggest the best way forward.  This could be done with a date table, or just a math formula.  Really depends on your data.

[GenericUser1]

[GenericUser1]

those columns are from a table called Task Execution. Also I am using a regular Calendar Date table. THanks!

[Anonymous]

Ok this should be pretty straight forward.  Your execution time is already precalculated for you.  This will make things easier, you can simply set up a measure to get the Average from that column and divide it by 60.  So at least you have the average execution time sorted.

Now you want to set up some time intelligence.  Since you have a a date table i'm expecting you want to use it. You will need a column that will join to that date table.  First thing you need to deciede:  Do you want to do your filtering based on the start or end date of the execution?  Once you have decieded that, create a new column that is a copy of the desired column and format it to be of data type "Date".

Now set up your visual making use of the average time formula.  On the visual itself, bring the date column into the visual filters and set the filter type to "Relative Date Filter".  Choose the time period (i.e. last 1 month).  If you want the user to be able to select the filter time period you could optionally put that date field into a slicer and set it to Relative filtering.

[GenericUser1]

I was hoping to figure out the rolling month for every single start execution date, for example, if that execution time is, 23/9/2018 I'd like to see the average between 23/9/2018 and 23/8/2018 . if it is 23/8/2018, then id like to see the average between 23/8/2018 nd 23/7/2018 do you get what I mean? I was hoping to use a stacked column chart to show this and it should look like the bars are smoothly rolled like a snake not like a sky cit scraper look if you get what I mean

[Anonymous]

Oh yeah thats also possible.  I'm expecting you mean something like this:

Sum Exe In Minutes = SUM('YourTable'[Job Execution Duration in Seconds]) * 60

Average Exe 2day Avg = CALCULATE( 
	AVERAGEX(
		values('Dates'[YearDayWeek]), 
		[Sum Exe in Minutes]
	),
	DATESINPERIOD(
		'Dates'[Date], 
		LASTDATE('Dates'[Date]), 
		-1, 
		DAY
	)
)

[GenericUser1]

*sky city scraper

[GenericUser1]

pretty much the average for every single start date & a months worth of data behind that start date.