Filter as of month keeping Open values and excluding paid values

[Anonymous]

Hi all,

I have the table below and I need to have a slicer "as of month" that when I filter Apr-2021 I need to show the value for Mar-2021 only where the Open Amount still open. 

I also have a date table in my model.

In the case below when filtered Apr-2021 also shows invoice b as it is still open but doesn't show invoice a which is already paid.

Invoice	Due date	Amount	Paid	Open Amount
a	Mar-21	2000	2000	0
b	Mar-21	1500	0	-1500
c	Apr-21	1000	0	-1000
d	Apr-21	3200	0	-3200

Is that possible?

Many thanks

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

I suggest having a separate slicer table, like below, in order to make the visualization act differently than what readers select in the slicer.

The measure is in the sample pbix file.

Open Amout Show =

VAR currentmonth =

MONTH ( SELECTEDVALUE ( Slicer[Due date - Copy], TODAY () ) )

VAR previousmonthamountnotclose =

CALCULATE (

SUMX ( Invoices, Invoices[Paid] - Invoices[Amount] ),

KEEPFILTERS (

FILTER (

ALLSELECTED ( Invoices ),

MONTH ( Invoices[Due date - Copy] ) = currentmonth - 1

)

)

)

RETURN

IF ( previousmonthamountnotclose <> 0, previousmonthamountnotclose, BLANK () )

https://www.dropbox.com/s/1rv5pk6v9jp28fu/fabnishiv3.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

@Jihwan_Kim Hi, thanks for your input.

I also need to keep showing the values for apr-2021

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I thought only showing the previous month's information.

Please change the = sign to >= , like below.

Open Amout Show =

VAR currentmonth =

MONTH ( SELECTEDVALUE ( Slicer[Due date - Copy], TODAY () ) )

VAR previousmonthamountnotclose =

CALCULATE (

SUMX ( Invoices, Invoices[Paid] - Invoices[Amount] ),

KEEPFILTERS (

FILTER (

ALLSELECTED ( Invoices ),

MONTH ( Invoices[Due date - Copy] ) >= currentmonth - 1

)

)

)

RETURN

IF ( previousmonthamountnotclose <> 0, previousmonthamountnotclose, BLANK () )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

That almost got the result I need.

The only issue having a disconnected table is because i also need to filter the march back if i need to

[amitchandak]

@Anonymous , Try a measure like

measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
calculate(countx(filter(values(Table[Invoice]), calculate(sum(table[Amout]) -sum(Table[Paid])) =0), [Invoice]),filter(Table, Table[due Date] <=_max))