Adding Column to Power Query

[deanbland]

Hi, 

I am trying to add a column to Power Query that says "Yes" or "No" depending on whether it is the current month or not. 

What I am trying to do is compare the current month's rag status with last months in one table. This is proving to be a challenge and so this is the only way I can think to do it? 

A sample dataset is below 

Activity ID	Export Date	DAI RAG	DAI Title
31774	06/01/2021	1	Team
31774	06/01/2021	1	Schedule
31774	06/01/2021	1	Project
31774	06/02/2021	2	Team
31774	06/02/2021	2	Schedule
31774	06/02/2021	2	Project

What the visual table will look like 

DAI Title	Current Month	Previous Month
Team	1	2
Schedule	1	2
Project	1	2

If you can see a better way to do this then please do let me know! 

Thanks 🙂 

[mahoneypat · Accepted solution]

Please see this video on how to add a yearmonth index column centered on today to your date table.  If you don't have a date table, adding one makes your life easier (see 2nd link).

Power BI Tales From The Front - Day/Week/Month/Quarter/Year Indices - YouTube

Creating a simple date table in DAX - SQLBI

You can then make a measure that calculates your expression in two variables (one with YearMonthIndex = 0 and the other = -1 and then use both variables in your Return to compare them.

Pat

[Ashish_Mathur]

Hi,

Download my PBI file from here.

Hope this helps.

[mahoneypat · Accepted solution]

Please see this video on how to add a yearmonth index column centered on today to your date table.  If you don't have a date table, adding one makes your life easier (see 2nd link).

Power BI Tales From The Front - Day/Week/Month/Quarter/Year Indices - YouTube

Creating a simple date table in DAX - SQLBI

You can then make a measure that calculates your expression in two variables (one with YearMonthIndex = 0 and the other = -1 and then use both variables in your Return to compare them.

Pat

[deanbland]

Hi @mahoneypat, 

Thank you for that - the Month Index is working a treat. My question is, how would I translate this into measures that return current and past months? I can't seem to figure out how to create the table in the above post that compares current to past. 

Thanks again! 

[mahoneypat · Accepted solution]

You could write two measures like this

Current Month = CALCULATE(SUM(Table[DAI RAG]), Date[YearMonthIndex] = 0)
Prev Month = CALCULATE(SUM(Table[DAI RAG]), Date[YearMonthIndex] = -1)

Another way to do it would be to make a matrix visual with DAI Title on Rows, YearMonthIndex on Columns, and drag the DAI RAG column to the Values and choose Sum aggregation.  You can then use the Filter panel to filter YearMonthIndex to between 0 and -1.

Pat