Create a summarize table filtered by last month

[cristianml]

Hi,

I want to create a table with some columns and the important thing is to be filtered by previous month. I started the idea but not sure if the correct way :

__TEST =

VAR V1 = SUMMARIZE('CCMS Billings append','CCMS Billings append'[Master Client Number],'CCMS Billings append'[Master Client Name])

VAR V2 = SELECTCOLUMNS('CCMS Billings append',,MONTH('CCMS Billings append'[Document Date]

The main idea is to get the list of all clients only from previous month.

Hope you can help me .

Thanks!

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship (Many to One and Single) from the Document Date column of the CCMS Billings append table to the Date column of the Calendar Table.  Write calculated column formulas in the Calendar Table to extract Year, Month Name and Month number.  Sort the Month name by the Month number.  Create 2 slicers - for Year and Month name.  Select 2022 and May.  Create a Table visual and drag Master Client Number and Master Client Name there.  Write this measure and drag it to the visual 

Measure 1 = countrows(filter(values('CCMS Billings append'[Master Client Number]),datesbetween(Calendar[Date],edate(min(calendar[date]),-1),min(calendar[date])-1)))

If Measure 1 does not work, then try this one

Measure 2 = countrows(calculatetable(values('CCMS Billings append'[Master Client Number]),datesbetween(Calendar[Date],edate(min(calendar[date]),-1),min(calendar[date])-1)))

Hope this helps.

[cristianml]

Hi @Ashish_Mathur ,

Just need to create this table with that information and then I'll use lookupvalue. 

I only need the expression to create the table with those 2 arguments : Summarize clients from last month.

An idea would be for current month we can use MAX and from that point It can be use MONTH -1 to get to the previous month. 

[Ashish_Mathur]

Hi,

i do not write tables formulas except when i am creating a Calendar Table.  I prefer only measures.  Does my solution not work for you?

[cristianml · Accepted solution]

Hi @Ashish_Mathur ,

Finally I solved it with 3 steps:

1- New Column in Main table:

2- Creation of calculated table with clients in last month:

3 - New column in Main table for new clients:

Thanks !

[cristianml]

Hi @Ashish_Mathur ,

I already have calendar table (Dates) but neither of the measures worked.

What I'm doing now is creating a column with the following formula:

__TEST =

VAR CURRENT_DATE = MAX('CCMS Billings append'[Document Date])

VAR CURRENT_MONTH = MONTH('CCMS Billings append'[Document Date]) & YEAR('CCMS Billings append'[Document Date])

VAR PREV_MONTH = MONTH(CURRENT_DATE)-1 & YEAR('CCMS Billings append'[Document Date])

RETURN

IF(CURRENT_MONTH=PREV_MONTH,"LAST",BLANK())

Then with this I can create a sumarize table with 2 columns: Client and this column filtered with "LAST". And then I can use Lookupvalue in the main table to identify those clients. 

Will let you know if doesn't work neither but It should.

Thanks.