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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Do value exist in the previous month

Hi,

 

I am struggeling to understand how I can check if a value/record exist in the previous month in a table. It seems like I have a solution for the first hit, using EARLIER, but if there is a gap between the months I am a bit lost.

(i.e. record exist in month 1,2 4 - should give result New, Update, New)

 

I have tried using the DAX formula below:

-------

Valid record status =
IF (
CALCULATE (
COUNTROWS ( testpricechangew ),
FILTER (
testpricechangew,
testpricechangew[Material] = EARLIER ( testpricechangew[Material] )
&& ( testpricechangew[Last date of previous month] )
> EARLIER ( testpricechangew[Last date of validtomonth] )
)
) = 1,
"New",
"Updated"
)

-------

 

Below is how my basic table looks like and the required output.

 

Appreciate all the assistance I can get!

 

Power BI tableOutput
MaterialPriceValid FromValid toRecord statusPrevious priceChange from previous priceEnd of record
Mat001123601.01.202124.01.2021New record   
Mat0011283,9625.01.202118.06.2021Updated record1236Price increaseEnd period
Mat001185903.08.202131.12.2021New record  End period
Mat00292401.01.202124.01.2021New record   
Mat002959,8525.01.202111.05.2021Updated record924Price increase 
Mat002265912.05.202131.05.2021Updated record959,85Price increase 
Mat002265901.06.202131.12.2021Updated record2659FlatEnd period
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

First you could add index column in power query ,then create a column by dax.

1.add index column in power query.

vyalanwumsft_0-1633330077189.png

2.create a coulmn by dax.

Valid record status =
VAR _diff =
    DATEDIFF (
        CALCULATE (
            MAX ( [Valid to] ),
            FILTER (
                'testpricechangew',
                [Material] = EARLIER ( [Material] )
                    && [Index]
                        = EARLIER ( [Index] ) - 1) ),
        [Valid From],
        MONTH)
RETURN
    IF (
        [Valid From]
            = CALCULATE (
                MIN ( [Valid From] ),
                ALLEXCEPT ( testpricechangew, testpricechangew[Material] )),
        "New record",
        IF ( _diff <= 1, "Updated record", "New record" ))

3.another dax

previous price2 = IF([Valid record status]="Updated record",CALCULATE(MAX([Price]),FILTER('testpricechangew',[Index]=EARLIER([Index])-1)))

The final output is shown below:

vyalanwumsft_1-1633330203684.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

First you could add index column in power query ,then create a column by dax.

1.add index column in power query.

vyalanwumsft_0-1633330077189.png

2.create a coulmn by dax.

Valid record status =
VAR _diff =
    DATEDIFF (
        CALCULATE (
            MAX ( [Valid to] ),
            FILTER (
                'testpricechangew',
                [Material] = EARLIER ( [Material] )
                    && [Index]
                        = EARLIER ( [Index] ) - 1) ),
        [Valid From],
        MONTH)
RETURN
    IF (
        [Valid From]
            = CALCULATE (
                MIN ( [Valid From] ),
                ALLEXCEPT ( testpricechangew, testpricechangew[Material] )),
        "New record",
        IF ( _diff <= 1, "Updated record", "New record" ))

3.another dax

previous price2 = IF([Valid record status]="Updated record",CALCULATE(MAX([Price]),FILTER('testpricechangew',[Index]=EARLIER([Index])-1)))

The final output is shown below:

vyalanwumsft_1-1633330203684.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi @v-yalanwu-msft 

 

This is good DAX approach to the challenge I presented.  

I need to read more about DAX to utilize this more. 

 

Unfortunately I found the challange a bit more complex than I first anticipated. However, I solved it by using much the same approach as you, but in power query, and by using three indexes.

 

Can't say if my solution is the best approach, but it seems to be working.

I'll upload hte pbix as example in case someone would like to see what has been done.

 

Link to pbix and xlsx file used 

lbendlin
Super User
Super User

EARLIER has not much to do with time. It refers to row context hierarchies.  Use DATEADD(,,-1,MONTH)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors