- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- 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
- Re: Last Month calculations do not work in January

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

jengwt

Member

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

01-07-2019
02:28 PM

I have a lot of calculation in a report which calculate stats for the "last [period of time]". Now that we have started a new year, a lot of these calculations seem broken.

I thought that these calcs would work in the new year because Dax's date/calendar math appears capable of handling math that spans different years and months. For instance, if you ask it for a date - 1 month and it the input as january, it correctly returns December. But this does not appear to be the case here.

Below you see a table detailing the numbers for certain periods of time. X1 is the average size of the population, X2 is the size of the losses in that period of time, and Ann Rate is the annualized rate of loss in that period. The periods in question are for the selected period (PRD) via a slicer, Month to Date, Year to Date, Last Month, Last Year, End of Month, and End of Year.

Here is the code for __ x1__, the population of a given data set:

Population_LM = -- STATIC CALC VAR LMDs = [LM_Days] -- Correctly retruns the number of days in the last month VAR DN = [DNOW] -- DNOW being the current date RETURN ROUND(CALCULATE(SUM('Population[COUNT]) , CALCULATETABLE('Population' , MONTH('BI-DATE_DIM'[DIM_DATE]) = (MONTH(DN) - 1) && YEAR('BI-DATE_DIM'[DIM_DATE]) = YEAR(DN) ) ) / LMDs, 0)

__ x2__, the count of losses:

Loss_LM = -- STATIC CALC VAR DN = [DNOW] RETURN CALCULATE(COUNT('Losses'[ID]) , CALCULATETABLE('Losses' , (MONTH('BI-DATE_DIM'[DIM_DATE]) = MONTH(DN) - 1) && (YEAR('BI-DATE_DIM'[DIM_DATE]) = YEAR(DN)) ) )

And __ Ann Rate__ is the annualized rate of loss, based on the calendar year:

TO_LM_ANN = ([Loss_LM]/[Population_LM]) * ([EOYrDay_LM] / [LM_Days]) -- STATIC calc. Works for all other metrics but this one. -- [EOYrDay_LM] correcly returns the number of days for the year of which last month was a part.

In other words, the rate for the given period of time * the number of days in the year / the length of the period is the annualized rate. This calculation works for all other metrics, so the problem must be that the code doesn't know how to calculate stats for last month when the last month was in another year.

**TABLES**

*+Sample data for @v-yuezhe-msft*

*Population* is a table which for simplicity's sake has counts contributing to a 'census', if you will.

As you can see, the total population for December was 50, and the population this far in January is 60.

Count | Date |

15 | 12/1/2018 |

16 | 12/15/2018 |

19 | 12/30/2018 |

35 | 1/1/2019 |

25 | 1/9/2019 |

*Losses* is just a table detailing events related to that population.

As you can see, there were 10 in December and 6 so far in January.

ID | Date |

Ashely | 12/4/2018 |

Naida | 12/6/2018 |

Violet | 12/13/2018 |

Pa | 12/14/2018 |

Alline | 12/16/2018 |

Jesusa | 12/16/2018 |

Lucrecia | 12/18/2018 |

Bronwyn | 12/20/2018 |

Gene | 12/21/2018 |

Velda | 12/26/2018 |

Ken | 1/1/2019 |

Rossie | 1/1/2019 |

Olevia | 1/3/2019 |

Patricia | 1/5/2019 |

Carylon | 1/9/2019 |

Bob | 1/10/2019 |

*BI-DATE_DIM* is simple a calendar table. It has 1:* relationships with the other tables with dates. You don't need to know why this is, but it is necesary.

DIM_DATE |

12/1/2018 |

12/2/2018 |

12/3/2018 |

12/4/2018 |

12/5/2018 |

12/6/2018 |

12/7/2018 |

12/8/2018 |

12/9/2018 |

12/10/2018 |

12/11/2018 |

12/12/2018 |

12/13/2018 |

12/14/2018 |

12/15/2018 |

12/16/2018 |

12/17/2018 |

12/18/2018 |

12/19/2018 |

12/20/2018 |

12/21/2018 |

12/22/2018 |

12/23/2018 |

12/24/2018 |

12/25/2018 |

12/26/2018 |

12/27/2018 |

12/28/2018 |

12/29/2018 |

12/30/2018 |

12/31/2018 |

1/1/2019 |

1/2/2019 |

1/3/2019 |

1/4/2019 |

1/5/2019 |

1/6/2019 |

1/7/2019 |

1/8/2019 |

1/9/2019 |

1/10/2019 |

Given these numbers, we would expect to get:

LM = 10 / 50 * 365 / 31 = 235%

Why not 20%? because I am required to use annualized numbers.

Solved! Go to Solution.

Report Inappropriate Content

Message 1 of 15

0

1 ACCEPTED SOLUTION

Accepted Solutions

jengwt

Member

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

01-22-2019
03:05 PM

Ok I figured it out. It's actually pretty simple; you just explicitely tell PBI what to do in January:

Population_LM = VAR LMDs = [LM_Days] VAR DN = [DNOW]

VAR MDN = MONTH(DN)

VAR YDN = YEAR(DN) RETURN ROUND( DIVIDE( CALCULATE( SUM('Population'[COUNT]) , CALCULATETABLE('Population' , MONTH('BI-DATE_DIM'[DIM_DATE]) = IF(MDN = 1 , 12 , MDN ) && YEAR('BI-DATE_DIM'[DIM_DATE]) = IF(MONTH(DN) = 1 , YDN - 1 , YDN ) ) ) , LMDs ) , 0 )

and,

Loss_LM = -- STATIC CALC VAR DN = [DNOW]

VAR MDN = MONTH(DN)

VAR YDN = YEAR(DN) RETURN CALCULATE( COUNT('Losses'[ID]) , CALCULATETABLE(Losses' , MONTH('BI-DATE_DIM'[DIM_DATE]) = IF(MDN = 1 , 12 , MDN ) && YEAR('BI-DATE_DIM'[DIM_DATE]) = IF(MDN = 1 , YDN - 1 , YDN ) ) )

I still think those PREVIOUS...() functions don't really work as envisioned.

14 REPLIES 14

v-yuezhe-msft

Moderator

Re: Last Month calculations do not work in January

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

01-08-2019
11:20 PM

@jengwt,

Could you please share sample data of your original table and post expected result here?

You can follow the guide in the blog below to share data.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lydia

Community Support Team _ Lydia Zhang

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

If this post

v-yuezhe-msft

Moderator

Re: Last Month calculations do not work in January - Status changed to: Needs Info

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

01-08-2019
11:20 PM

If this post

jengwt

Member

Re: Last Month calculations do not work in January

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

01-10-2019
09:04 AM

@v-yuezhe-msft I posted some sample data to the OP.

v-yuezhe-msft

Moderator

Re: Last Month calculations do not work in January

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

01-15-2019
01:51 AM

@jengwt,

I create the following measures in your date table.

LASTCOUNT = CALCULATE(COUNT(Losses[ID]),PREVIOUSMONTH(DIM_DATE[DIM_DATE]))

LASTSUM = CALCULATE(SUM(Population[Count]),PREVIOUSMONTH(DIM_DATE[DIM_DATE]))

LM = [LASTCOUNT]/[LASTSUM]*365/31

And create the following column in the date table.

YearMonth = FORMAT(DIM_DATE[DIM_DATE],"YYYY-MM")

Regards,

Lydia

If this post

jengwt

Member

Re: Last Month calculations do not work in January

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

01-15-2019
08:25 AM

@v-yuezhe-msft I tried modifying my report to use these calculations, and not only do they still yield (Blank), they actually they break my existing calculations which worked using my code.

Seems like PREVIOUSMONTH(), PREVIOUSQUARTER(), and PREVIOUSYEAR() don't work either.

What is the point of YearMonth? What do you do with that? Oh, that's because it's the determining row in the matrix? Unfortunately, we're not displaying the data in that way.

v-yuezhe-msft

Moderator

Re: Last Month calculations do not work in January

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

01-15-2019
11:34 PM

@jengwt,

The PREVIOUSMONTH(), PREVIOUSQUARTER(), and PREVIOUSYEAR() functions will return correct result with date context. If you don't want to put yearmonth column in the Matrix, you can create a slicer using the yearmonth column and select value in the slicer.

Regards,

Lydia

If this post

jengwt

Member

Re: Last Month calculations do not work in January

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

01-17-2019
09:15 AM

@v-yuezhe-msft I don't understand, are those functions somehow dependent on YearMonth? And when I modify my report to include PreviousMonth() and PreviousYear(), the measures return (BLANK).

Highlighted
##

Community Support Team _ Lydia Zhang

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

v-yuezhe-msft

Moderator

Re: Last Month calculations do not work in January

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

01-18-2019
01:47 AM

@jengwt,

Yes, these functions will return blank value if you don't use date type fields(Date, Year, YearMonth,etc) to filter them. You can include yearmonth slicer in your report.

Regards,

Lydia

If this post

jengwt

Member

Re: Last Month calculations do not work in January

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

01-18-2019
07:30 AM

@v-yuezhe-msft Literally all I did was swap in those functions. For example:

Population_LM = VAR LMDs = [LM_Days] --VAR DN = [DNOW] RETURN ROUND(CALCULATE(SUM('Population[COUNT]) ,('BI-DATE_DIM'[DIM_DATE])PREVIOUSMONTH/* CALCULATETABLE('BI-DATE_DIM' , MONTH('BI-DATE_DIM'[DIM_DATE]) = (MONTH(DN) - 1) && YEAR('BI-DATE_DIM'[DIM_DATE]) = YEAR(DN) ) */) / LMDs, 0)

'BI-DATE_DIM'[DIM_DATE] is a mm/dd/YYYY field.

Before:

After: