topic Re: I want to fetch data for last week and last month (week wise and month wise data) in DAX Commands and Tips

I want to fetch data for last week and last month (week wise and month wise data)

Faeez — Wed, 18 Dec 2024 17:03:29 GMT

I would like to get the data week wise and month wise i already tried slicer and got it but i want to display data only for past week and past month without slicer and using DAX query.

To achieve

DAX query to get data for last week.
DAX query to get data for last month.
DAX to get P1 reported due to change

INC Number	Priority	Created	Workgroup	Description	Change
INC20001	1-Critical	15-12-2024	Network L2	Network issue
INC20002	1-Critical	14-12-2024	DB L2	DB issue	CHN-1234
INC20003	1-Critical	13-12-2024	Network L2	Network issue
INC20004	1-Critical	15-12-2024	DB L2	DB issue	CHN-1234
INC20005	1-Critical	14-12-2024	Network L2	Network issue
INC20006	1-Critical	8/12/2024	DB L2	DB issue
INC20007	1-Critical	7/12/2024	Network L2	Network issue
INC20008	1-Critical	6/12/2024	DB L2	DB issue	CHN-1234

Re: I want to fetch data for last week and last month (week wise and month wise data)

lbendlin — Wed, 18 Dec 2024 19:28:00 GMT

You will have to define what you mean by "last" and "week". What day does your week start?

Consider using DATEADD(xxx,-7, DAY) for the week and DATEADD(xxx,-1,MONTH) for the month.

Re: I want to fetch data for last week and last month (week wise and month wise data)

Anonymous — Thu, 19 Dec 2024 01:49:44 GMT

Hi Faeez ,

I think you can use New table. on top of that you can apply CALCULATETABLE function.

I create a table as you mentioned.

Then I create three new tables and here are their DAX codes.

LastWeekData = CALCULATETABLE( 'Table', 'Table'[Created] >= TODAY() - WEEKDAY(TODAY(), 2) - 6 && 'Table'[Created] < TODAY() - WEEKDAY(TODAY(), 2) + 1 )

LastMonthData = CALCULATETABLE( 'Table', 'Table'[Created] >= EOMONTH(TODAY(), -1) + 1, 'Table'[Created] <= EOMONTH(TODAY(), 0) )

P1Change = CALCULATETABLE( 'Table', 'Table'[Priority ] = "1-Critical", NOT(ISBLANK('Table'[Change ])) )

Best Regards

Yilong Zhou

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

Re: I want to fetch data for last week and last month (week wise and month wise data)

Faeez — Thu, 19 Dec 2024 10:57:58 GMT

count of incident no reported this month ( specific month) - 1 to 31st
week - Monday to Sunday

Re: I want to fetch data for last week and last month (week wise and month wise data)

Kedar_Pande — Fri, 20 Dec 2024 14:02:54 GMT

Faeez

LastWeekData = 
CALCULATE(
COUNTROWS('Table'),
'Table'[Created] >= TODAY() - WEEKDAY(TODAY(), 2) - 7 &&
'Table'[Created] < TODAY() - WEEKDAY(TODAY(), 2)
)

LastMonthData = 
CALCULATE(
COUNTROWS('Table'),
'Table'[Created] >= EOMONTH(TODAY(), -2) + 1 &&
'Table'[Created] <= EOMONTH(TODAY(), -1)
)

P1DueToChange = 
CALCULATE(
COUNTROWS('Table'),
'Table'[Priority] = "1-Critical" &&
NOT(ISBLANK('Table'[Change]))
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Re: I want to fetch data for last week and last month (week wise and month wise data)

Faeez — Mon, 23 Dec 2024 12:16:10 GMT

For getting data P1 due to change

the tickets inside the table are by default P1 issues so i want to get a count of P1 which are caused due to change. if you the see the table out of 4 issues there are 2 tickets which are caused due to P1. Hence the output i should is 2 but the DAX which you gave is not filtering the count rather giving the overall count.
could you help?

Re: I want to fetch data for last week and last month (week wise and month wise data)

Kedar_Pande — Mon, 23 Dec 2024 13:53:21 GMT

Faeez

You can try:

P1DueToChange = 
CALCULATE(
COUNTROWS('Table'),
'Table'[Priority] = "1-Critical",
NOT(ISBLANK('Table'[Change])) 
)

Re: I want to fetch data for last week and last month (week wise and month wise data)

Faeez — Wed, 01 Jan 2025 13:19:59 GMT

for some reason i am getting the overall count rather than getting the count which was caused by change, also i might need the query to get the data for current month which from calendar 1st to 31st

Re: I want to fetch data for last week and last month (week wise and month wise data)

Faeez — Thu, 02 Jan 2025 16:40:46 GMT

count of incident no reported this month ( specific month) - 1 to 31st
week - Monday to Sunday

could you help me with this one?