Using Max Month in a table visualization

[Anonymous]

Hi, 

I have a table which uses a max month to show the current month values and prior month values.

It is supposed to present the number of journal entries posted by a user.

The top level is the cost cente name, and the drill down are the users.

The formula I have for the current month is:

VAR SMonth = if(ISFILTERED('Calendar'[Month Name]),SELECTEDVALUE('Calendar'[Month name],[MaxM Name JE]),[MaxM Name JE])

VAR CDN = CALCULATE (COUNTAX('Journal Lists',[Document Number]),  FILTER(('Journal Lists'), 'Journal Lists'[Month Name]=SMonth))

The issue is that the table presents the max month per user. So say the max month for whole data is 'October'.  And User A only posted until 'September'.  For User A, the table will show the September figure in the 'Current month' column and August in the 'previous month' column which is causing the inconsistency.

Appreciate anyone who can help me with this.  Thanks a lot!!

See screenshot below:

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,
 
First you need to get the value of the year and month of the date:
year = YEAR(Sheet1[date])

month = MONTH(Sheet1[date])
 
Then get the maximum month for each user's data:

current_month = CALCULATE(

                    MAX(Sheet1[month]),

                    FILTER(Sheet1,

                    Sheet1[year]=EARLIER(Sheet1[year])&&

                    EARLIER(Sheet1[username])=Sheet1[username]))

Determine if it is current month or prior month:

prior_current_month = IF(Sheet1[month]=Sheet1[current_month],"Current Month",

                        IF(Sheet1[month]=Sheet1[current_month]-1,"Prior Month"))

 
Create measure to compute the sum:

sum_month = IF(

                MAX(Sheet1[prior_current_month])="Current Month",

                    CALCULATE(

                        SUM(Sheet1[value]),

                        Sheet1[prior_current_month]="Current Month"),

                        IF(

                            MAX(Sheet1[prior_current_month])="Prior Month",

                                CALCULATE(

                                    SUM(Sheet1[value]),

                                    Sheet1[prior_current_month]="Prior Month")))

 
You can also refer to the pbix.
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.