How to create a date measure showing previous month

[Anonymous]

I have a date table called 'Date Table' with a Date column called [Date]

I want to use this table to create a measure which shows the previous month

So, as of today it's December 10 2019 so last month is November 2019. I want this date measure to be dynamic so as soon as it's 1st January I want it to show 'December 2019'

Is this possible?

Thanks

Jemma

[Ashish_Mathur]

Hi,

So if it is December 11, 2018 today, you want to show the Sales for the period November 1 - 30 2019?  Am i correct?

[Anonymous]

@Ashish_Mathur I'm not looking to display sales, just the month and year value for the previous month. So literally 'November 2019' is what I want the measure to show. 

[Ashish_Mathur]

Hi,

In a card visual, drag this measure

=TEXT(EOMONTH(TODAY(),-1),"mmm-yy")

Hope this helps.

[Anonymous · Accepted solution]

@Ashish_Mathur  that measure doesn't like the TEXT query - it is red underlined and the error says the syntax is incorrect. See screenshot. However =FORMAT etc seems to work, so I've used this!

[v-juanli-msft]

Hi @Anonymous 

If you want to show all dates of previous month on a tbale visual, you could follow steps below:

create measures and add the measure to the viusal level filter,

today = TODAY()

show all dates of previous month = IF(DATEDIFF(MAX('date'[Date]),[today],MONTH)=1,1,0)

Or if you want to show the previous month's value, you could create a measure

previous month's value = CALCULATE(SUM('Table'[value]),FILTER('date',DATEDIFF('date'[Date],[today],MONTH)=1))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft I want to show the previous month's value so I tried your second solution however i'm getting May 5496! My date table only goes up to the current date so I have no idea how this happened.

[v-juanli-msft]

Hi @Anonymous 

As tested, even if i only have date till today, it works on my side, could you provide more information so i can analyze further?

measures

today = TODAY()

Measure = FORMAT(EOMONTH(TODAY(),-1),"mmmm yyyy")

Measure 2 = CALCULATE(SUM('Table'[value]),FILTER('Table',DATEDIFF('Table'[date],[today],MONTH)=1))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Try

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date Filer],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi @amitchandak  this has something to do with sales and I am looking for a month name....

[Tahreem24]

Youcan use previousmonth dax function to achieve your requirements. Syntax of dax is give below :
Prevoiusmonth(DateTable[Datecolumn])

Please give Kudos and accept this as a solution if it helps you.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a method of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...