Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ezh0ng
Frequent Visitor

How can I capture change data and give sequence

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.

 

YYYYMMSKULOTPRICE
202001MM01F110
202001MM01F210
202001MF01D15
202002MM01F112
202002MM01F210
202002MF01D15
202003MM01F112
202003MM01F29
202003MF01D15
202004MM01F112
202004MM01F210
202004MF01D14

 

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

 

YYYYMMSKULOTPRICESTATUSRANK
202001MM01F110changed2
202001MM01F210changed4
202001MF01D15changed2
202002MM01F112changed1
202002MM01F210changed3
202002MF01D15  
202003MM01F112  
202003MM01F29changed2
202003MF01D15  
202004MM01F112  
202004MM01F210changed1
202004MF01D14changed1

 

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.

1 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:

15.PNG

 

Best Regards,

Giotto Zhi

View solution in original post

8 REPLIES 8
v-gizhi-msft
Community Support
Community Support

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:

36.PNG

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.

35.PNG

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

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.

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:

5.PNG

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

@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.

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:

15.PNG

 

Best Regards,

Giotto Zhi

TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens, Thanks for reply.

I want to give seq by YYYYMM desc by SKU.

 

Please check the table below.

 

YYYYMMSKULOTPRICESTATUSRANK(seq)
202004MF01D14changed1
202001MF01D15changed2
202002MF01D15  
202003MF01D15  
202002MM01F112changed1
202001MM01F110changed2
202003MM01F112  
202004MM01F112  
202004MM01F210changed1
202003MM01F29changed2
202002MM01F210changed3
202001MM01F210changed4

 

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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.