Snapshot Measure count rows from history table

[Anonymous]

Hi,

My Requirement is below.

My Table data

SQL Query

Select @AsonDate as AsonDate,count(TicketID) as OpenTickets 
from tblTicket o 
where Status<>'Closed'
and o.ChangedDate<=@AsonDate 
and o.ChangedDate=(Select max(i.ChangedDate) from tblTicket i where i.ChangedDate<=@AsonDate and i.TicketID=o.TicketID )

My Dax Query

I have tried below query but no luck

No of Open Tickets:=
VAR AsonDate= MAX(DIM_DATE[Date])

VAR Tickets = CALCULATE(COUNTROWS(Ticket), LASTDATE(Ticket[ChangedDate]), VALUES (Ticket[TicketID]), ALL (Ticket),
            FILTER(Ticket,Ticket[Status] <> "Closed"),
            FILTER(Ticket,Ticket[ChangedDate]<=AsonDate)
           )

RETURN
IF(ISBLANK(Tickets), 0, Tickets)

Please Help me.

Thanks..

[Anonymous]

Refer below link, the same scenario https://stackoverflow.com/questions/55381222/dax-formula-for-sql-with-correlated-subquery?rq=1

[dax]

Hi RajapriyanK,

Below is my design:

accountid          status       date          amount

1	new	1/1/2019	10
1	inprogress	1/2/2019	5
1	closed	1/3/2019	2
1	open	1/4/2019	4
1	inprogress	1/5/2019	5
1	closed	1/6/2019	8
2	new	1/1/2019	7
2	inprogress	1/2/2019	6
2	open	1/4/2019	4
2	inprogress	1/5/2019	5
2	closed	1/6/2019	8
2	inprogress	1/3/2019	2
2	inprogress	1/7/2019	3

create a calenadr table by 

calenadr = CALENDAR(DATE(2019,1,1), date(2019,1,30))

Then create a measure like below

Measure 5 =
VAR maxchange =
    CALCULATE (
        MAX ( Ticket[date] ),
        FILTER ( ALL ( Ticket ), Ticket[date] <= MIN ( calenadr[Date] ) )
    )
RETURN
    CALCULATE (
        SUM ( Ticket[amount] ),
        FILTER (
            ( Ticket ),
            Ticket[accountid] = MIN ( Ticket[accountid] )
                && Ticket[status] <> "closed"
                && Ticket[date] = maxchange
        )
    )

Then you will get below result

If you want to get result like your first reply, you could try below measure

Measure 5 = 
VAR maxchange =
    CALCULATE (
        MAX ( Ticket[date] ),
        FILTER ( ALL ( Ticket ), Ticket[date] <= MIN ( calenadr[Date] ) )
    )
RETURN
    CALCULATE (
        COUNT('Ticket'[accountid]),
        FILTER (
            ( Ticket ),
           
                 Ticket[status] <> "closed"
                && Ticket[date] = maxchange
        )
    )

Best Regards,
Zoe Zhi

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