- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Calculated Table for Month over Month Changes

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

xzfujc

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-03-2018
12:30 PM

Hello,

I have an archive table that looks like the following:

Gate | Emp. ID | Count | Month | Year |

IA | a123 | 351 | 9 | 2018 |

IRA | a123 | 220 | 9 | 2018 |

NEW | a123 | 26 | 9 | 2018 |

PSA | a123 | 108 | 9 | 2018 |

IRA | b456 | 39 | 9 | 2018 |

NEW | b456 | 151 | 9 | 2018 |

PSA | b456 | 55 | 9 | 2018 |

IA | a123 | 453 | 9 | 2018 |

IRA | a123 | 125 | 10 | 2018 |

NEW | a123 | 35 | 10 | 2018 |

PSA | a123 | 99 | 10 | 2018 |

IA | b456 | 20 | 10 | 2018 |

IRA | b456 | 9 | 10 | 2018 |

NEW | b456 | 109 | 10 | 2018 |

PSA | b456 | 66 | 10 | 2018 |

I want to create a calculated table to show the Month over Month Change in the count of "Gates" from month 9 to 10 by Employee ID (Emp. ID). So I want a table that will look like the following:

Gate | Emp. ID | Month Over Month Change |

IA | a123 | 102 |

IRA | a123 | -95 |

NEW | a123 | 9 |

PSA | a123 | -9 |

IA | b456 | 20 |

IRA | b456 | -30 |

NEW | b456 | -42 |

PSA | b456 | 11 |

This would allow me to show how things are moving from one gate to another each month for each employee and through the entire department. How can I code this in DAX so that the count from the Maximum Month in the table subracts the count from the previous month for each gate and employee combination?

I would need code to take into consideration how employee b456 has no IA gate in month 9 but has a count of 20 in the IA gate by month 10. Also I would need the code to take into consideration if the month is 1 then to subtract the count from month 12 of the previous year.

Any advice would be appreciated!

Solved! Go to Solution.

Report Inappropriate Content

Message 1 of 8

1 ACCEPTED SOLUTION

Accepted Solutions

dedelman_clng

New Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-03-2018
12:48 PM

Hi @xzfujc

The best way is to think of measures instead of tables/columns. You can then calculate the number of gates for the given month, the number of gates for the previous month, and then the difference.

You will need a date column to key off of, but since you have Month and Year, that's easy enough to create.

//Calculated Column Dt = DATE(Year, Month, 1) //Measures Total Gates = CALCULATE(SUM(Count)) LM Total Gates = CALCULATE([Total Gates], PREVIOUSMONTH(Dt)) Variance = [Total Gates] - [LM Total Gates]

This will allow you to create a table visual, and if you want to look at it by just Gate instead of by Gate and Emp ID, the calculation will take care of itself.

Hope this helps

David

7 REPLIES 7

dedelman_clng

New Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-03-2018
12:48 PM

Hi @xzfujc

The best way is to think of measures instead of tables/columns. You can then calculate the number of gates for the given month, the number of gates for the previous month, and then the difference.

You will need a date column to key off of, but since you have Month and Year, that's easy enough to create.

//Calculated Column Dt = DATE(Year, Month, 1) //Measures Total Gates = CALCULATE(SUM(Count)) LM Total Gates = CALCULATE([Total Gates], PREVIOUSMONTH(Dt)) Variance = [Total Gates] - [LM Total Gates]

This will allow you to create a table visual, and if you want to look at it by just Gate instead of by Gate and Emp ID, the calculation will take care of itself.

Hope this helps

David

xzfujc

Frequent Visitor

Re: Calculated Table for Month over Month Changes

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2018
06:57 AM

I think your solution will accomplish what I need.

I am running into an issue with

LM Total Gates = CALCULATE([Total Gates], PREVIOUSMONTH(Dt))

It returns a 0. So my total variance is now equal to my total gates. Working on resolving. Is there a way to say something like the following?

Total Gates = CALCULATE(SUM(Count), CurrentMonth(Dt))

dedelman_clng

New Contributor

Re: Calculated Table for Month over Month Changes

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2018
07:08 AM

Change the variance calculation to

Variance = IF(ISBLANK([LM Total Gates]), 0, [Total Gates] - [LM Total Gates])

(or use BLANK() instead of 0 if you want to show no value in Variance for the first month)

Hope this helps

David

xzfujc

Frequent Visitor

Re: Calculated Table for Month over Month Changes

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
07:35 AM

Really appreciate all the help, and I do believe the solution you provided is the solution I need. I am having trouble fully implementing it though, for some reason I can not get the LM Gates Total to sum anything which gives me a variance of 0.

Is there any reason you could think of why the the LM Total Gates returns null?

v-cherch-msft

Community Support Team

Re: Calculated Table for Month over Month Changes

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2018
11:51 PM

Hi @xzfujc

You may drag the 'Dt' column to the table visual instead of 'Month' column.

LM Total Gates = CALCULATE([Total Gates], PREVIOUSMONTH(Dt))

Regards,

Cherie

Community Support Team _ Cherie Chen

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

If this post

dedelman_clng

New Contributor

Re: Calculated Table for Month over Month Changes

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-09-2018
09:07 AM

Also, you should have a dedicated CALENDAR table that has a relationship to Dt on your data table in order to fully utilize time intelligence like PREVIOUSMONTH()

xzfujc

Frequent Visitor

Re: Calculated Table for Month over Month Changes

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2018
11:53 AM

I can't explain it, but adding the .[Date] to the Dt field solved my problem. After adding the .[Date], the LM value was calculated correctly.

LM Total Gates = CALCULATE([Total Gates], PREVIOUSMONTH('Gates Count Archive'[Dt].[Date]))

Thanks for all your help!