How can I capture change data and give sequence

[ezh0ng]

Hi.

I want to make notation when price changed and give seqencial number also.

Because, I want to see data changed in the last 6 times.

My data looks like this.

YYYYMM	SKU	LOT	PRICE
202001	MM01	F1	10
202001	MM01	F2	10
202001	MF01	D1	5
202002	MM01	F1	12
202002	MM01	F2	10
202002	MF01	D1	5
202003	MM01	F1	12
202003	MM01	F2	9
202003	MF01	D1	5
202004	MM01	F1	12
202004	MM01	F2	10
202004	MF01	D1	4

My plan was notation change first, and give ranks them desc order like below.

YYYYMM	SKU	LOT	PRICE	STATUS	RANK
202001	MM01	F1	10	changed	2
202001	MM01	F2	10	changed	4
202001	MF01	D1	5	changed	2
202002	MM01	F1	12	changed	1
202002	MM01	F2	10	changed	3
202002	MF01	D1	5
202003	MM01	F1	12
202003	MM01	F2	9	changed	2
202003	MF01	D1	5
202004	MM01	F1	12
202004	MM01	F2	10	changed	1
202004	MF01	D1	4	changed	1

I made STATUS column,

STATUS =
VAR lastStatus =
    CALCULATE(
    MIN([PRICE])
    , PREVIOUSMONTH('D_Dates'[YYYYMMDD])
    , ALLEXCEPT(FACTTABLE, 'FACTTABLE'[SKU], 'FACTTABLE'[LOT])
    )
RETURN
    IF([PRICE]=lastStatus, "", "Changed")

but RANK.

I'm tyring apply RANKX() many ways, but, I can see error messages only.

My approach is correct? and How I can give seq to changes?

Thanks for reading, and I will wait advice from yours.

Regards.

[v-gizhi-msft]

Hi,

According to your description, please take following steps:

1)Create two columns in FactTable:

YYYYMM = FORMAT(FactTable[Date],"YYYYMM")
MonthNo = FactTable[Date].[MonthNo]

2)Create a Status measure:

Status = 
VAR LastMonthPrice =
    CALCULATE (
        MAX ( FactTable[PRICE] ),
        FILTER (
            ALLSELECTED ( FactTable ),
            FactTable[SKU] IN FILTERS ( FactTable[SKU] )
                && FactTable[LOT] IN FILTERS ( FactTable[LOT] )
                && FactTable[MonthNo]
                    = MAX ( FactTable[MonthNo] ) - 1
        )
    )
RETURN
    IF (
        MONTH ( MAX ( FactTable[Date] ) )
            = MONTH ( MINX ( ALL ( FactTable ), FactTable[Date] ) ),
        "Changed",
        IF ( MAX ( FactTable[PRICE] ) = LastMonthPrice, "", "Changed" )
    )

3)Create a Rank measure:

Rank = 
VAR a =
    RANKX (
        FILTER (
            ALLSELECTED ( FactTable ),
            [Status] <> BLANK ()
                && FactTable[SKU] IN FILTERS ( FactTable[SKU] )
                && FactTable[LOT] IN FILTERS ( FactTable[LOT] )
        ),
        CALCULATE ( SUM ( FactTable[MonthNo] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( [Status] <> BLANK (), a, BLANK () )

4)The result shows:

Tips: I think the row's status marked by red line in your expected result should be blank not "Changed" because under the same [SKU] and [LOT], the previous month price is the same with this row's price as 10.

So the result should show like my above screenshot.

If i have misunderstand your requirement and logic, please for free to let me know.

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[ezh0ng]

Hi, @v-gizhi-msft.

That's are measures what I want! Thanks you very much 🙂

If you don't mind, can you help me to apply that to calculated columns?

Because I want to filtering data by change or not. Measures cannot be filter on page, so I need to make that columns.

But, when I attempted, a message returned, "A circular dependency was detected"

Or, there are some way to apply measure to page filter?

Regards,

Jihong.

[v-gizhi-msft]

Hi,

Please try this column:

Column = 
VAR LastMonthDate =
    DATE ( YEAR ( [Date] ), MONTH ( [Date] ) - 1, DAY ( [Date] ) )
VAR LastMonthPrice =
    CALCULATE (
        SUM ( FactTable[PRICE] ),
        FILTER (
            ALLSELECTED ( FactTable ),
            FactTable[SKU] = EARLIER ( FactTable[SKU] )
                && FactTable[LOT] = EARLIER ( FactTable[LOT] )
                && FactTable[Date] = LastMonthDate
        )
    )
RETURN
    IF (
        MONTH ( FactTable[Date] )
            = MONTH ( CALCULATE ( MIN ( FactTable[Date] ), 'FactTable' ) ),
        "Changed",
        IF ( FactTable[PRICE] = LastMonthPrice, "", "Changed" )
    )

The result shows:

And then you can easily filter this status column in page filter pane.

If my answer has solved your issue, please mark it as a solution for others to see.

Thanks!

Best Regards,

Giotto Zhi

[ezh0ng]

@v-gizhi-msft,

I'm sorry. I didn't have enough explanation.

I wanna add RANK (seq) column also.

To do so, I can add limit condition or slicer filter.

(ex. show history of change until 3 time, or 6time...)

I've been in trouble for two days, but your advice pull me out from.

Regards,

Jihong.

[v-gizhi-msft · Accepted solution]

Hi,

Please try this rank column:

Column-Rank = 
VAR a =
    RANKX (
        FILTER (
            ALLSELECTED ( FactTable ),
            FactTable[Column-Status] <> BLANK ()
                && FactTable[SKU] = EARLIER ( FactTable[SKU] )
                && FactTable[LOT] = EARLIER ( FactTable[LOT] )
        ),
        FactTable[MonthNo],
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( FactTable[Column-Status] <> BLANK (), a, BLANK () )

The result shows:

Best Regards,

Giotto Zhi

[TomMartens]

Hey @ezh0ng ,

can you please elaborate on the ranking, I have to admit that I do not understand how the ranking (sequence) is composed?.

Regards,

Tom

[ezh0ng]

@TomMartens, Thanks for reply.

I want to give seq by YYYYMM desc by SKU.

Please check the table below.

YYYYMM	SKU	LOT	PRICE	STATUS	RANK(seq)
202004	MF01	D1	4	changed	1
202001	MF01	D1	5	changed	2
202002	MF01	D1	5
202003	MF01	D1	5
202002	MM01	F1	12	changed	1
202001	MM01	F1	10	changed	2
202003	MM01	F1	12
202004	MM01	F1	12
202004	MM01	F2	10	changed	1
202003	MM01	F2	9	changed	2
202002	MM01	F2	10	changed	3
202001	MM01	F2	10	changed	4

[amitchandak]

Create a this month and last month measure and use that in rank. to get this month and last moth rank

Get a date first

date = Date(left([YYYYMM],4),right([YYYYMM],2),1)

Use this with date calendar

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Now create two rank measure

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/