SQL to DAX - iterative table query

[pandapanda]

I have SQL like this

select count(*) from myTABLE f1 join myTABLE f2
  on f1.customerid = f2.customerid
where f1.reportingdate='2017-10-31' and f2.reportingdate='2017-10-30'
  and f1.currentstatus='1' and f2.currentstatus <>'1'

I want to get this as a measure in PowerBI desktop.

The dates should be dynamic from a date heirachy slider (table already created)

How can i get this is DAX

[parry2k]

@pandapanda 

Can you share sample data for both the tables?

[pandapanda]

Its only 1 table!! i am using it against itself to work out differences

The table is like this

ID       customerID   Date                Status

1             1212          2/3/2016         1

2             1213          2/3/2016         2

3             1212          2/4/2016         4

4             1213          2/4/2016         4

For every day, you get row for each customer

over time the status changes

What I want to know is the changes to and from status. 

so i want on PowerBI a number where the number of changes to status X happens over a period of time

[parry2k]

i guess in this sample your output will be

Status   #of changes

1                 1

2                 1

4                 2

correct?

[pandapanda]

Yes that is correct, but what I want in the report is to show how many users have changed to status (dynamic/slicer to choose) over a certain time perid.

in the data i give, 

If I were to select the last 60 days and want to know how many users moved to status 2 then the answer I want is 2!

[parry2k]

based on your sample date , answer will be 1 the scenario you explained below, no?

[pandapanda]

Your previous post: 

Status   #of changes

1                 1

2                 1

4                 2

correct?

----------------

Those are the right numbers - i want to get that last result -  #changes is 2 where status is 4 for that time period!

[parry2k]

@pandapanda in table visual add status, and then add status again and change the aggregation of 2nd status to distinct count

add date slicer and your table visual will filter based on the date range you select in date slicer

[pandapanda]

For the three use cases

1.start is 8th nov and end date is 12th November  there are 2 two customers (id 1 and 2) that change to Regular hence  the answer is 2

2. 8th nov and end date is 10th November no one changes to regular hence 0 (in fact here id3 gooes to sleeper)

3. Expected result if date start is 11th nov and end date is 12th November is 2 NOT 1 as per the sample sheet answer!! as id1 and 3 go to regular

Sorry!!

but i am sure the main data set will be easier to get some numbers

[v-caliao-msft · Accepted solution]

@pandapanda,

It's seems someone provide a solution for your requirement on your another thread, pelase check it.
http://community.powerbi.com/t5/Desktop/Report-number-of-changes-in-a-field/m-p/310698#M137616

number of users that change to currentstatus regular =
VAR summizedTbl =
    SUMMARIZE (
        yourTable,
        yourTable[customerid],
        "Distinct Status count", DISTINCTCOUNT ( yourTable[currentstatus] ),
        "status", CONCATENATEX (
            yourTable,
            yourTable[currentstatus],
            "/",
            yourTable[reportingdate], DESC
        )
    )
VAR rowCnt =
    COUNTROWS (
        FILTER (
            summizedTbl,
            [Distinct Status count] > 1
                && LEFT ( [status], 7 ) = "Regular"
        )
    )
RETURN
    IF ( ISBLANK ( rowCnt ), 0, rowCnt )

Regards,

Charlie Liao 

[pandapanda]

I have tried  - I add time slicer based on a time table and use it but it gives me really low number ( am expecting 00's not 1 or 2)

[pandapanda]

The two users  1212  and 1213  both change to status 4  (from status 1 and 2 ) in the time period (60days) 

Is that clear?