Previous Month's Count and 2 Months Before

[Kasun78]

Hello

I have a dataset consisting of number of emails and is received time, date, etc. I've already created a separate Month and Year table for the convenience of the visuals I've already created. (Note that all columns in the month and year table are in text format and I cannot convert it to date.)

Now I wanted to create a measure that calculates the NUmber of emails sent in the previous month and 2 beofre months based on the month the user is selecting (Slicer was created using the month and year tables mentioned above)

I tried to use all the methods (PreviousMonth, DateAdd) that are mentioned in the forum and I could not solve this, as it is giving me an error when I select this month table saying that the choosen column was in text format.

Could someone please be able to help me?

P.S - Below is an example of what my data looks like.

https://drive.google.com/file/d/1tHMVWPlpaO2pOBTe5gSKEokN2w2okB51/view?usp=drivesdk

[Kasun78]

Hi Liu @v-yangliu-msft ,

I'm having another requirement for the same data set. Thought updating this rather than posting a new thread.

I was using the above formula which you have provided and now I wanted to add a visual level filter. (for an example I'm having team names for each of the row and I wanted my card visual which uses the above formula to filter it using visual filters by team name) But it's not working.

I can't use the team name like you've showed in the example because I wanted to use drilldown option and when I use the team name at the end of the formaula dril down option doesn't give the corect value.

Aprreciate your help on this.

[v-yangliu-msft]

Hi  @Kasun78 ,

I added a company field in the data:

Here are the steps you can follow：

1. Create measure.

lastmount =
var _1 = SELECTEDVALUE('Table'[Month])
VAR _2= SELECTEDVALUE('Table'[Company])
var _lastmount=
CALCULATE(SUM('Table'[Email time]),FILTER(ALL('Table'),'Table'[Month]=_1-1&&'Table'[Company]=_2))
return _lastmount

first two months =
var _1=SELECTEDVALUE('Table'[Month])
VAR _2=SELECTEDVALUE('Table'[Company])
var _lastmount1= CALCULATE(SUM('Table'[Email time]),FILTER(ALL('Table'),'Table'[Month]=_1-1&&'Table'[Company]=_2))
var _lastmount2=CALCULATE(SUM('Table'[Email time]),FILTER(ALL('Table'),'Table'[Month]=_1-2&&'Table'[Company]=_2))
return _lastmount1+_lastmount2

2. Result.

Put the month and company fields into Slice

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[v-yangliu-msft · Accepted solution]

Hi  @Kasun78 ,

According to your description, I created some data to show:

Here are the steps you can follow：

1. Create calculcated column.

Month = MONTH('Table'[Date])

2. Create measure.

lastmount =
var _1 = SELECTEDVALUE('Table'[Month])
var _lastmount=
CALCULATE(SUM('Table'[Email time]),FILTER(ALL('Table'),'Table'[Month]=_1-1))
return _lastmount

first two months =
var _1=SELECTEDVALUE('Table'[Month])
var _lastmount1= CALCULATE(SUM('Table'[Email time]),FILTER(ALL('Table'),'Table'[Month]=_1-1))
var _lastmount2=CALCULATE(SUM('Table'[Email time]),FILTER(ALL('Table'),'Table'[Month]=_1-2))
return _lastmount1+_lastmount2

3. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[Kasun78]

Hi Liu, 

Thank you so much. You saved my day. 😀

I amended it a bit as below to cater to few other requirements of me.

lastmount = 
var _1 = SELECTEDVALUE(email[Month_Metrics])
var _2 = SELECTEDVALUE(email[Year])
var _lastmount=
CALCULATE(COUNTROWS(email),FILTER(ALL(email),email[Month_Metrics]=_1-1),email[Year]= _2, email[Team] = "HR")
return _lastmount

Thanks again

[ChrisMendoza]

@Kasun78 - The use of Time Intelligence Functions in DAX have 'requirements'. Take a look at https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-mon... to start learning how to use.