Is it possible to reference a cell in the PowerBI DAX?

[yanpereira]

Hello everybody,

I started my apprenticeships at PowerBI this week and I already have a big challenge for me. The scenario is as follows:

I have a database that has the following information:

1. Account code

2. Types of transactions

3. Date of each transaction

I need to create a fourth column that will return the date of the last transaction for that account code and so I can classify each account in a fifth column as:

A. active account: traded this month;
B. inactive account: did not trade that month;

It probably has a better way, but my head still works a lot like excel.

In Excel I would do it in a single expression:

MAXIF ([3. Date of each transaction]; [1. Account code]; [1. Account code]); where the first two arguments are columns and the third references the cell.

Can you please help me translate this to PowerBI DAX?

Thanks

Yan P.

[Ashish_Mathur]

Hi,

Share a dataset and sho the expected result.

[PhilipTreacy · Accepted solution]

Hi @yanpereira 

This can be done with Measures rather than columns. It's better to use measures when you can. 

Some reading on the difference between measures and columns Calculated Columns and Measures in DAX - SQLBI

As you haven't provided sample data I've created some - download the sample PBIX file here

For the Last Transaction use this DAX

Last Transactions = CALCULATE(MAX(Table1[Order Date]), FILTER(ALL('Table1'), 'Table1'[CustNumber] = SELECTEDVALUE('Table1'[CustNumber])))

and to check if they are active use this

Active Inactive = IF(EOMONTH([Last Transactions],0) = EOMONTH(TODAY(),0), "Active", "Inactive")

This is the data with the results of the above DAX measures

Regards

Phil

[Tarun13]

Hi Philip,

Could you please advise me.

I have a dataset which is stored in dataverse and am performing write back operation using the power app. Everyting is working fine but in one column I need to calculate the value which is the some percentage of the preious stage.  As shown in the below table.

Stages	next Stage (%)	Volume (per stage)
A	70% 0r 0.7	1000 ( this is input value or use can change this value
B	40 % or 0.4	700 (70% of 1000)
C	20% or 0.2	280 (40% of 700)
D		56 (20% of 280)

As Volume column is a caclulate columnn in which fist value is default value or user input, and the rest values are depends on the previous stages percentage.

Please help how can I create this column using measure or if statement or using any other Dax formula. 

I would really apreciate you help.