topic Re: FILTER and PREVIOUSMONTH function DAX , how to combine in DAX Commands and Tips

FILTER and PREVIOUSMONTH function DAX , how to combine

Eric-De — Tue, 18 Jul 2023 09:30:41 GMT

Under powerautomate I used the PowerBI dataset to export some values under excell file, and I need to filter on the previousmonth only.

For this I have create a calendar table to compare with the date closed under the table Tickets but when I implent the

FILTER ( Tickets, Tickets[closed] = PREVIOUSMONTH(Calendar[Date]))

instead of

FILTER ( Tickets, Tickets[closed] < DATE(YEAR(today()),MONTH(today()),1) && Tickets[closed] >= DATE(YEAR(today()),MONTH(today())-1,1) )

that return nothing

Here my script DAX who running

Can you help me to use corectly the PREVIOUSMONTH function for this ?

// DAX Query

DEFINE

VAR vType = TREATAS({"CPU", "MEM","DISK"}, Tickets[Service])

EVALUATE

SUMMARIZECOLUMNS

(

ROLLUPADDISSUBTOTAL

(

Tickets[Service], "Type", vType,

TREATAS ( { "OK" }, Tickets[Status] ),

FILTER( Tickets , RELATED(ServiceOffering[Service Offering]) ="TOP" ),

FILTER ( Tickets, Tickets[closed] < DATE(YEAR(today()),MONTH(today()),1) && Tickets[closed] >= DATE(YEAR(today()),MONTH(today())-1,1) )

"Total Qty", COUNT(Tickets[Target])

)

ORDER BY

[Type] DESC, [Service]

Re: FILTER and PREVIOUSMONTH function DAX , how to combine

lbendlin — Sat, 22 Jul 2023 01:37:43 GMT

DEFINE VAR vType = TREATAS({"CPU", "MEM","DISK"}, Tickets[Service]) EVALUATE SUMMARIZECOLUMNS ( ROLLUPADDISSUBTOTAL ( Tickets[Service], "Type", vType, TREATAS ( { "OK" }, Tickets[Status] ), FILTER( Tickets , RELATED(ServiceOffering[Service Offering]) ="TOP" ), FILTER ( Dates, PREVIOUSMONTH(Dates[Date] ) ), "Total Qty", COUNT(Tickets[Target]) )

This assumes you have a Dates table in your data model and that it is joined to the Close Date.

Re: FILTER and PREVIOUSMONTH function DAX , how to combine

Eric-De — Mon, 24 Jul 2023 06:43:57 GMT

Hello

So need to create a table date for this

CALENDAR (

DATE ( YEAR ( MIN ( Tickets[Closed] ) ), 1, 1 ),

DATE ( YEAR ( MAX ( Tickets[Closed] ) ), 12, 31 )

)

and because column containt date and time problably need o separate the date and time to group result by month

But in all case thank for your reply