Dash board help

[kasun_rumesh]

Hi,

I'm calculating selected date over 365 debtors using below dax and its giving me results which i wanted.

CALCULATE (

SUM ( BICus[Amount] ),

FILTER (

BICus,NOT ( DATEADD ( DocumentDates[Date], 365, DAY ) >MAX(DateDim[Date])))

I want to calculate the over 365 debtors for last month end without changing present date selection. I have try below dax and its not given me the results which i expected can anyone help me on this.

CALCULATE (

SUM ( BICus[Amount] ),

FILTER (

BICus,

NOT ( DATEADD ( DocumentDates[Date], 365, DAY ) >EOMONTH( MAX(DateDim[Date]),-1)))

[kasun_rumesh]

Hi @v-zhangti ,

My date selection is 25-Feb-2022

Sample data set
Customer	DocumentDates[Date]	DateDim[Date]	Document no
A	13-Jan-21	13-Jan-21	IN-01
A	13-Jan-21	25-Jan-22	PY-01
A	13-Jan-21	23-Feb-22	PY-02
A	13-Jan-21	28-Mar-22	PY-03
		last month end Over 365 (31-Jan-2022)	Over 365 as at  25-Feb-2022
		13,000	11,000
		Expected results	This one getting from above DAX

[v-zhangti]

Hi, @kasun_rumesh 

What kind of BICus[Amount] field you mentioned in your formula and can you provide sample data? Are these tables connected by the Date field?

Best Regards

[v-zhangti]

Hi, @kasun_rumesh 

Can you provide some of the example data? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

Best Regards

[kasun_rumesh]

My Date selection is 25-Feb-22

Customer	InvoiceDate	PostingDate	DocumentNo	InvoiceNo	BICus[Amount]
A	13-Jan-21	13-Jan-21	IN-01	IN-01	15,000
A	13-Jan-21	25-Jan-22	PY-01	IN-01	(2000)
A	13-Jan-21	23-Feb-22	PY-02	IN-01	(2000)
A	13-Jan-21	28-Mar-22	PY-03	IN-01	(2000)

Last Month over 365 (31-Jan-22)	Balance as at 25-Feb-22
13,000	11,000
Searching results	This one getting from first dax

Yes, I have two date table. I have relationship as below. There's no relationship between two table

InvoiceDtae->DocumentDates[Date]

PostingDate->DateDim[Date]