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

[Faeez]

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

[Kedar_Pande]

@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

[Faeez]

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?

[Kedar_Pande]

@Faeez 

You can try:

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

[Faeez]

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

could you help me with this one?

[Faeez]

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

[Anonymous]

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.

[lbendlin]

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.

[Faeez]

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