topic Re: Help with DAX forumula in DAX Commands and Tips

Help with DAX forumula

Anonymous — Wed, 08 Apr 2020 15:39:36 GMT

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'

Pragati11 — Wed, 08 Apr 2020 16:17:37 GMT

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

az38 — Wed, 08 Apr 2020 16:28:36 GMT

Hi Anonymous

or create a new table

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

amitchandak — Fri, 10 Apr 2020 08:57:16 GMT

Try like

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