CODEWITHSUNDEEP
daxpowerbi-desktop
Billy Jones
Billy Jones

Reputation: 23

Filtering date/time frame using DAX in PowerBI

I have a task to create a card that shows a value in last 2 days with cutoff time of 8PM to 8 PM?

How can I do this using DAX ?

Example:

Today is 06/04/2022.

I have a row with date/time stamp.

I wan to select rows with timestamp 04/04/2022 8:00 PM to 05/04/2022 8:00 PM to display the count of rows in a card visual.

This is to be reported everyday, so I want to make it dynamic so I don't have to change it everyday.

Thanks

Upvotes: 0

Views: 2200

Answers (1)

intruderr
intruderr

Reputation: 353

Try the measure:

Countrows = 
VAR maxDT = MAX( Data[Datetime] )
VAR prevDay = DATE( YEAR( maxDT ), MONTH( maxDT ), DAY( maxDT ) - 1 )
VAR cutOffDT_ub =  CONVERT( FORMAT( maxDT, "yyyy-MM-dd") & " 8:00 PM", DATETIME )
VAR cutOffDT_lb = CONVERT( FORMAT( prevDay, "yyyy-MM-dd") & " 8:00 PM", DATETIME )
VAR result = 
    COUNTROWS(
        FILTER(
            ALL ( Data ) ,
            Data[Datetime]  >= cutOffDT_lb && Data[Datetime] <= cutOffDT_ub
        )
    )
return result

Output:
enter image description here

Is that what you want?

Upvotes: 1

Related Questions