Add new column with filters based on current row values

[JohnYeldham]

My main question here is around how to filter based on a value taken from the current column.

In this case I have a column Dates.Month (which is a whole number in the format YYYYMM) and a new calculated column Dates.MonthBefore (which is a whole number in the format YYYYMM, showing the code for the previous month).  So in one row, is the Dates.Month is 202001, then Dates.MonthBefore is 201912.  This bit works OK.

How do I create a new column that extracts the value from rows, looking for Dates.Month in the searched rows being equal to Dates.MonthBefore in the current row?  The problem with Filter is it looks at hardcoded values only.

I want something like:

OpeningBalance = calculate(SUM(Table[Balance]), Table[Dates.Month] = Table[Dates.MonthBefore][thisrow])

I am sure there must be an easy answer to this!

Cheers

[Icey · Accepted solution]

Hi @JohnYeldham ,

Try this:

Column =
VAR LastMonth = 'Table'[Dates.BeforeMonth]
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Dates.Month] = LastMonth )
    )

or,

Column =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Dates.Month] = EARLIER ( 'Table'[Dates.BeforeMonth] )
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JohnYeldham]

Thanks, I used the first one.  It was very similar to my code, but with the added "ALL('Table')" and an explicit FILTER (rather than implicit in the CALCULATE) - and that seems to have done the trick.

[amitchandak]

@JohnYeldham , in case you need a measure you can convert this into date

date = date(left([month year],4),right([month year],2),1)

Then you can use time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

you also use new column 

sumx(filter(table,& eomonth([Date],0) = eomonth(arlier([Date]),-1)),[Amount])

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Appreciate your Kudos.

also refer : https://youtu.be/yPQ9UV37LOU

[JohnYeldham]

This is probably the way to go long term, but @Icey's solution is quicker for now.  I will certainly revisit your suggestion at the next time calculation.