cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
xzfujc Frequent Visitor
Frequent Visitor

Calculated Table for Month over Month Changes

Hello,

 

I have an archive table that looks like the following:

 

GateEmp. IDCountMonthYear
IAa12335192018
IRAa12322092018
NEWa1232692018
PSAa12310892018
IRAb4563992018
NEWb45615192018
PSAb4565592018
IAa12345392018
IRAa123125102018
NEWa12335102018
PSAa12399102018
IAb45620102018
IRAb4569102018
NEWb456109102018
PSAb45666102018

 

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: 

 

GateEmp. IDMonth Over Month Change
IAa123102
IRAa123-95
NEWa1239
PSAa123-9
IAb45620
IRAb456-30
NEWb456-42
PSAb45611

 

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
dedelman_clng New Contributor
New Contributor

Re: Calculated Table for Month over Month Changes

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
New Contributor

Re: Calculated Table for Month over Month Changes

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
Frequent Visitor

Re: Calculated Table for Month over Month Changes

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
New Contributor

Re: Calculated Table for Month over Month Changes

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)

 

Capture.PNG

 

Hope this helps

David

xzfujc Frequent Visitor
Frequent Visitor

Re: Calculated Table for Month over Month Changes

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. 

image.png

 

image.png

 

image.png

 

 

image.png

 

image.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

v-cherch-msft Super Contributor
Super Contributor

Re: Calculated Table for Month over Month Changes

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.
dedelman_clng New Contributor
New Contributor

Re: Calculated Table for Month over Month Changes

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
Frequent Visitor

Re: Calculated Table for Month over Month Changes

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!

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 287 members 2,806 guests
Please welcome our newest community members: