Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
birdie29
Helper II
Helper II

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

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

 

First Transactions2.jpg

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

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")

 

11.jpg

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

View solution in original post

3 REPLIES 3
v-huizhn-msft
Employee
Employee

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")

 

11.jpg

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

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 Smiley Happy

 

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

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

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.