topic Re: How to find Last Month Value Dynamically in DAX Commands and Tips

How to find Last Month Value Dynamically

Anonymous — Tue, 08 Sep 2020 09:38:32 GMT

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

Re: How to find Last Month Value Dynamically

amitchandak — Tue, 08 Sep 2020 09:39:11 GMT

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.

Re: How to find Last Month Value Dynamically

Anonymous — Tue, 08 Sep 2020 10:40:04 GMT

amitchandak

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

Previous Month() its return wrong value

any guss?

Re: How to find Last Month Value Dynamically

amitchandak — Tue, 08 Sep 2020 10:46:46 GMT

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

Take my file and check. Attached after signature

Re: How to find Last Month Value Dynamically

Greg_Deckler — Tue, 08 Sep 2020 13:26:46 GMT

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

Re: How to find Last Month Value Dynamically

Anonymous — Tue, 08 Sep 2020 13:42:12 GMT

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?

Re: How to find Last Month Value Dynamically

Greg_Deckler — Tue, 08 Sep 2020 14:30:37 GMT

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.

Re: How to find Last Month Value Dynamically

Anonymous — Wed, 09 Sep 2020 06:18:45 GMT

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