How to find Last Month Value Dynamically

[Anonymous]

i need last month,   Ticket closed Count

i have tried below DAX but it return all the values i need puticular one month value only

Tickets Closed (Current Month -1) =
VAR _CurrentDate = MAX('Table'[RequestClosedDate])
VAR _StartMonth = EOMONTH(_CurrentDate,-1)+1
VAR _EndMonth = EOMONTH(_StartMonth,0)+1
VAR _Count =
CALCULATE(
DISTINCTCOUNT(Table'[TicketNo]),
'Table'[RequestClosedDate] >= _StartMonth
&& 'Table'[RequestClosedDate] <= _CurrentDate
)
RETURN

_Count

is any way to solve this matter? Thanks in advance!

Regards,

Rajesh

[Greg_Deckler]

@Anonymous - I guess I would think:

Tickets Closed (Current Month -1) =
VAR _CurrentDate = MAX('Table'[RequestClosedDate])
VAR _StartMonthDate = EOMONTH(_CurrentDate,-1)
VAR _StartMonth = DATE(YEAR(_StartMonthDate),MONTH(_StartMonthDate),1)
VAR _EndMonth = EOMONTH(_StartMonth,-1)
VAR _Count =
CALCULATE(
DISTINCTCOUNT(Table'[TicketNo]),
'Table'[RequestClosedDate] >= _StartMonth
&& 'Table'[RequestClosedDate] <= _CurrentDate
)
RETURN

_Count

[Anonymous]

Hi @Greg_Deckler 

it's return all the value i need last month ticket closed value only, 

 i have ticktno and Request closed date colum ,  i want to distinct count of how many tickets are closed last Month ,  (request closed month column is a date field)

any guss?

[Greg_Deckler]

@Anonymous - Let's back-up. Sample data, expected output.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@Greg_Deckler  @amitchandak 

i have attached Sample data Click here  

i need result like this (3 Month ticket closed Details) - i have already created calender table

Unique TicketID Count Monthly basis

Ticket Closed Current Month	Ticket Closed Last Month	Ticket Closed Last Month -1
12	52	3

Thanks & Regards,

Rajesh R

[amitchandak]

@Anonymous , join you close date with date table and use time intelligence

Examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

//Without Time Intelligence 
Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)	
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

@amitchandak 

i have tried EOMONTH(),  and Previousmonth() but it's not working

Previous Month() its return wrong value

any guss?

[amitchandak]

@Anonymous , It should work with Date calendar marked as Date.

Take my file and check. Attached after signature