Calculate new measure based on values from two columns

[khans9]

Hi all,

Each month, I have the Year-to-Date amount that was spent. I would like to calculate the amount spent in a specific month. I am creating a dashboard which will allow the user to filter based on month. 

My data looks like this:

Name	Month	YTD Spent
Michael	January	100
Laura	January	200
Lionel	January	150
Michael	February	100
Laura	February	500
Lionel	February	200

I would like it to look this: 

Name	Month	YTD Spent	Monthly Spent
Michael	January	100
Laura	January	200
Lionel	January	150
Michael	February	100	0
Laura	February	500	300
Lionel	February	200	50

This way, for example in the month of February, users can see how much was spent just in February.

Thanks in advance!

Best regards.

[mwegener · Accepted solution]

Hi @khans9 ,

I would solve it with Power Query.

1. Generate the Month No with a Conditional Column

2. Subtract 1 from the Month No, to get the Previous Month No

3. Merge Queries as new (Self-Join Name & Prev Month No --> Name & Month No)

4. Expand an get the previous YTD Spent

5. Subtract YTD Spent with previous YTD Spent

6. Rename the Column

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi @khans9 ,

did you solve your problem?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[dax · Accepted solution]

Hi khans9, 

You could refer to my sample for details. If this is not what you want, please correct me and inform me more detailed information.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwegener · Accepted solution]

Hi @khans9 ,

I would solve it with Power Query.

1. Generate the Month No with a Conditional Column

2. Subtract 1 from the Month No, to get the Previous Month No

3. Merge Queries as new (Self-Join Name & Prev Month No --> Name & Month No)

4. Expand an get the previous YTD Spent

5. Subtract YTD Spent with previous YTD Spent

6. Rename the Column

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.