topic How to write DAX query for getting last 30 days from a column not from Date table in DAX Commands and Tips

How to write DAX query for getting last 30 days from a column not from Date table

Radhika_Kanaka — Fri, 20 May 2022 19:24:38 GMT

Hi, I have a requirement, where I need the last 30 days data based on a date column that is not from Date table.

My DAX query is :

Medium_Resolution_Timestamp =

Var ART=calculate(AVERAGE(Source[MinutesRoundTotal]),filter(source,Source[Priority]="Medium"),DATESINPERIOD(Source[Resolved At].[Date],TODAY(),-30,DAY),'Calendar'[WeekDay- Weekend]="Weekday")

Var Result=if(isblank(Res),"No Data Available" & UNICHAR ( 10 ) &"Data displayed is: last 30 days since" & " (" & (today()-30) &")"& ". Priority is: Medium ",FORMAT(Res,"HH:MM" )& UNICHAR ( 10 ) & UNICHAR ( 10 ) & "HH:MM")

Return UNICHAR(10)

& Result

My requirement is to find the last 30 days avg resolution time with reference to 'Resolved At ' column. (Consider Resolved At date and find last 30 days data. For example, I need to find the data from 4/20 to5/20 baed on Resolved At column)

Please let me know if I can use DatesinPeriod for this .

Thanks,

Radhika Lanka

Re: How to write DAX query for getting last 30 days from a column not from Date table

jeroendekker — Fri, 20 May 2022 19:50:11 GMT

Hi
Dates in Period should reference a full data column in your Calendar.

I guess something like this.

DATESINPERIOD ( Calender[Date], LASTDATE(Source[Resolved At]), -30, DAY )

I hope this helps.

P.s It helps If you format your DAX. Try www.daxformatter.com for tips.

Re: How to write DAX query for getting last 30 days from a column not from Date table

Radhika_Kanaka — Mon, 23 May 2022 18:25:17 GMT

Thank you for all the responses. I am able to resolve the issue.