How to count the status changes within a table

[frankiekho]

Hi, i am quite new to power bi, and have a problem with this dax

i got a little task to count how many machines are being newly scrapped and newly delivered.

from the dataset i attach below, when the status from previous month is working and then this month the status is scrapped, then it should count one

and so with the newly delivered.

so from the dataset below, it should count

newlyscrapped: 1

and newly delivered: 1

any idea?

Thank you in advance

Serial(OEM)	Start of the Month	Status
1	01.01.2024	working
1	01.02.2024	scrapped
2	01.12.2023	working
2	01.01.2024	working
2	01.02.2024	working
3	01.12.2023	not delivered
3	01.01.2024	not delivered
3	01.02.2024	working

[v-jianpeng-msft · Accepted solution]

Hi, @frankiekho 

You can try the following calculation list DAX expression. According to your reply with the previous Super User, and the Sample Data you provided:

DAX formula:

New column = 

var _Date = 'Sheet1'[Start of the Month]

var _status = CALCULATE(SELECTEDVALUE('Sheet1'[Status]),FILTER(ALL(Sheet1),'Sheet1'[Serial(OEM)]=EARLIER(Sheet1[Serial(OEM)])&&'Sheet1'[Start of the Month]<EARLIER(Sheet1[Start of the Month])))
return

if( 'Sheet1'[Status]<> _status ,

Switch(true(),

[Status]= "scrapped"&&_status="working", "Newly scrapped",

[Status]= "working"&&_status="not delivered", "Newly Delivered"

), BLANK()

)

The calculation results are as follows:

If the above DAX expression can help you, it is really good. I provided the pbix file I used this time.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[amitchandak]

@frankiekho ,  You can use this column and use in the measure

New column =

var _Date = [Start of month]

var _status = maxx(filter(Table, [Serial(OEM)] = earlier([Serial(OEM) && eomonth([Start of month],0) = eomonth([Start of month],-1) ), [Status])

return

if( [Status]<> _status ,

Switch(true(),

[Status]= "scrapped", "Newly scrapped",

[Status]= "working", "Newly Delivered"

), "Same"

)

For Measure

you have to create same using this month vs prior month's status and again compare and

new measure =

var _curr = CALCULATE(Max(Table[Status]),DATESMTD('Date'[Date]))

var _last = CALCULATE(Max(Table[Status]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

return

if( _curr<> _last ,

Switch(true(),

_curr= "scrapped", "Newly scrapped",

_curr= "working", "Newly Delivered"

), "Same"

)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[frankiekho]

Hi Amit,

thanks for your reply, but it doesn't work.

let say there is a machine serial "4" which is already scrapped last month, so the status today is still "scrapped", with calculation above, it return "newly scrapped" 2 times for this machine #4

and there are also no "newly delivered" status shown.

i got a chance to pivot the table and it becomes something like

Serial | Start of the Month | not delivered | working | scrapped |

1        |   01.01.2024            |           null      |  1           |     null      |  

1        |   01.02.2024            |          null       |    null     |         1       |

and so on

do you somehow have idea to work on that ?