Help with DAX forumula

[Anonymous]

I have the following table

DateTransaction	DateForecast	Amount	Type
01/01/2010	03/01/2010	100	A

I want to show the Amount where there is a lag of 2 months and the type  = A.

The SQL for this would be (but I'm not sure what the DAX would be):

SELECT Amount from table

WHERE 

Datetransaction = Dateadd(month,2,DateForecast)

and type = 'A'

[amitchandak]

Try like

measure =
calculate(sum(Table[Amount]), filter(all(Table),DATEDIFF(Table[DateTransaction],Table[DateForecast],Month)>=2 && Table[Type] ="A"))

[az38]

Hi @Anonymous 

or create a new table

NewTable = 
FILTER(ALL(Table),
DATEDIFF(Table[DateTransaction], Table[DateForecast], MONTH) = 2 && Table[Type] = "A"
)

[Pragati11]

Hi @Anonymous ,

You can create a column in your data and use it as flag on your report:

IsAmountBasedOnLag = IF( table[Datetransaction] = DATEADD(table[DateForecast], 2, month), table[Amount] && table[Type] = "A", 1, 0)

The above dax expression creates a flag with 1 or 0 values in it. If your condition is met it is 1, esle it is 0.

On you report, add a report level filter for IsAmountBasedOnLag = 1. This will display relevant details as required.

NOTE: Replace table in above DAX with your table name.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati