Create a Column That Signifies the First Month's Transactions Based on Two Criteria

[birdie29]

Hi Everyone

What I'm trying to create is column that signifies (ie Yes/No) the first month's transactions based on two criterias, the 'Project ID' and the 'Project Model'.

I've given an example of what I would want the 'First Month's BASE Transactions' column to say below. In this case state 'Yes' where for each different 'Project ID',  the 'Project Model' is 'BASE' and the 'Project Transaction Date' is in the first month, otherwise state 'No'.

I thought of maybe using FIRSTDATE formula, but then our transactions are by day and I would want to capture all the first months transactions. Does anyone have any suggestions please?

Thank you so much in advance.

Chris

[v-huizhn-msft · Accepted solution]

Hi @birdie29

You should calculate the min month in each Project ID and the Project Model’s value equals BASE. Then compare current transaction with the first transaction in previous month, and calculate the 'First Month's BASE Transactions' value using IF function. I reproduce the scenario and get expected result as follows.

First, I create the calculated column Month and Min Month.

Month = MONTH('Transaction'[Project Transaction Date])

Min Month = CALCULATE(MIN('Transaction'[Month]),ALLEXCEPT('Transaction','Transaction'[Project ID]))

Then I create a another column 'First Month's BASE Transactions. In the logicaltest of IF function, it will return “Yes” or “No” based on comparing the Month and Min Month. Please review the formula and screenshot below.

First Month's BASE Transactions = IF('Transaction'[Project Model]="BASE",IF('Transaction'[Month]='Transaction'[Min Month],"Yes","No"),"No")

Please note, the date in last row of your given sample data is 2016/11/31, while there are 30 day in November actually. So I change the date to 2016/11/30.

If you have any question, please feel free to ask.

Best Regards,
Angelia

[birdie29]

Hi @v-huizhn-msft

Thank you so much for your reply! The detail you provided has really helped me to understand DAX just that bit more

There are just a couple of examples where the above doesn't work, the first being if the forecast data crosses years ie the first month of forecast data could be in October 16 or Month 10 however if there is also data in January 17 that will give the min month value of 1 so overides the true minimum month of 10. This was easy to correct as I just used the DATEVALUE function.

The above adjustment I made however causes an issue when there are transactions that occur before the first BASE transaction. For example if the first BASE transaction is the 10th October but there is a transaction that occurs before using a different Model ID, the Min month column will use the earlier transaction as the minimum date. Is there a way of altering the formula so it only provides a Min month date if the Model ID is also BASE?

I do have a work around for the time being as I can put the actual and forecast data in seperate tables but I'm keen to know a solution to the above as I think that will really help me going forward!

Thank you once again for your support, I really appreciate it 🙂

Chris

[birdie29]

For anyone that's interested I solved my other issue by concatenating the year and month as a number as opposed to using the DATEVALUE I initially tried and failed with.

Thanks again to @v-huizhn-msft for you help!

Chris