Forum Discussion

frankiekho's avatar
frankiekho
Helper I
2 years ago
Solved

How to count the status changes within a table

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 MonthStatus
101.01.2024working
101.02.2024scrapped
201.12.2023working
201.01.2024working
201.02.2024working
301.12.2023not delivered
301.01.2024not delivered
301.02.2024

working

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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-bd52912a5bd4
    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's avatar
      frankiekho
      Helper I
      2 years ago

      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 ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.