Do value exist in the previous month

[Anonymous]

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 table	Output
Material	Price	Valid From	Valid to	Record status	Previous price	Change from previous price	End of record
Mat001	1236	01.01.2021	24.01.2021	New record
Mat001	1283,96	25.01.2021	18.06.2021	Updated record	1236	Price increase	End period
Mat001	1859	03.08.2021	31.12.2021	New record			End period
Mat002	924	01.01.2021	24.01.2021	New record
Mat002	959,85	25.01.2021	11.05.2021	Updated record	924	Price increase
Mat002	2659	12.05.2021	31.05.2021	Updated record	959,85	Price increase
Mat002	2659	01.06.2021	31.12.2021	Updated record	2659	Flat	End period

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

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

1.add index column in power query.

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:

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]

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]

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