Create a measure that shows me the previous month

[rosscortb]

Hello

Any ideas how i create  a measure to show the previous month count.

Thanks
Ross

[NadaFathy193]

if i need to calculate the last year what should i do

[Mark_Berry]

I also have simliar issue as to the OP. I have used the supplied DAX to try and return a count of incidents for the previous month only and I recieve the total count up to the previous month. 
I have also used this DAX as a base to try and solve my issue = CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTime'[DateKey])) 

Any help would be appreciated.

[v-frfei-msft · Accepted solution]

Hi @rosscortb,

I made one sample for your reference. Please check the following steps as below.

1. Create a date table, and create relationship between it and the fact table.

date = CALENDARAUTO()

2. To create the measure to get the amount of the previous month.

Previous = CALCULATE(SUM(Table1[headcount]),DATEADD('date'[Date],-1,MONTH))

Please check the pbix as attached.

Regards,

Frank

[sidvix917]

Hi,

Similar to your query, mine aims to retrieve the most recent non-blank value. This formula works well and takes the value from the prior month when I input it. However, it won't pick it up if there are two or more consecutive months with a blank value. Can you explain a procedure that does that?

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.

[sidvix917]

Hey Ashish,

I was able to find the answer, but I still have a question. The following measure was effective:

Last Non Blank Value =

Var Last_Date =

CALCULATE(MAX(Dates[Date]),

FILTER(ALL(Dates), Dates[Date] <= MAX(Dates[Date]) &&

Query1[Total Sales Values] <> 0))

return

CALCULATE([Sales Value], FILTER(ALL(Dates),Dates[Date] = Last_Date))

 now require the measure to stop filling after a specific date. I obtain the information from an outside source that provides me with the most recent market pricing. I therefore want the fill down stop say after October 2023. See the illustration below:

[Ashish_Mathur]

Hi,

Try this

Last Non Blank Value =
Var Last_Date =
CALCULATE(MAX(Dates[Date]),
FILTER(ALL(Dates), Dates[Date] <= MAX(Dates[Date]) &&
Query1[Total Sales Values] <> 0))

return
if(min(calendar[date])>=date(2023,10,1),blank(),CALCULATE([Sales Value], FILTER(ALL(Dates),Dates[Date] = Last_Date)))

[sidvix917]

This gives me an error. 'Too many arguments were passed to the MIN function. Also, it shouldn't stop in October. Every month I update this report and gives me a price for the current month.

return
if(min(calendar[date]>=date(2023,10,1),blank(),CALCULATE([Sales Value], FILTER(ALL(Dates),Dates[Date] = Last_Date)))

[Ashish_Mathur]

Hi,

I edited my previous reply - pleease copy that formula again.

[sidvix917]

Hi Ashish,

That is great! Is there a way to have it stop after the final value? So, for example, I have one category that no longer displays pricing after February 2019. So I'm not going to fill it until October 2023. Similarly, I have another category with a price cap of November 2022, and I want it to stop filling after that date.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Clearly show the problematic visual there.

[sidvix917]

Hi Ashish,

I am unable to share the file. But I can show you the table.

So, in the original, when I pick category 1, it gives me pricing from January 1, 2000, to December 31, 2018. However, you can see the missing month marked in yellow, where I fill in the number from the prior month. So in this case, it would be 115.00, and the 490.00 I indicated is the most recent price change for category 1. So I don't want the measure to fill down from the last number and on.

It may differ for other categories with other dates, and I don't want the measure to fill down after the last price. The formula you gave me contained October 2023, which is obviously what I gave you, but it was only an example. The dates are subject to change.

The table below displays the measure you provided, however as you can see, the fill-down works but continues past 12/2018, which I don't want. I hope this clarifies the situation.

[Ashish_Mathur]

I cannot help you by just reading some text and looking at screenshots.

[sidvix917]

Hey Ashish,

I understand. I have attached a sample data file. Please find the link below:

https://we.tl/t-4Kf20Fvr6b

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[sidvix917]

Hi Ashish,

Thank you very much. It works perfectly.

I have one last question. How would I be able to get 3 months rolling average for that measure?

The values that are being filled down should be included in the rolling average.

Thanks

[Ashish_Mathur]

You are welcome.  Since this is a different question, please start a new thread.

[JGG]

Hi, I beleive there at two challenges with this DAX. It gives not only previous but previous and earlier. And if you have dates in the future it will not return previous but previous from max date. So my question is how to get values from previous month only refered to sysdate/to day?

Previous = CALCULATE(SUM(Table1[headcount]),DATEADD('date'[Date],-1,MONTH))

[rosscortb]

it doesn't seem to work for me. In your example you have typed in the headcount while my headcount is based on individuals rows so its counting those rows to work out the headcount for each month 

[v-frfei-msft]

Hi @rosscortb,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank