cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Patron

## Create a measure that shows me the previous month

Hello

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

Thanks
Ross

1 ACCEPTED SOLUTION
Community Support

Hi @rosscortb,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
24 REPLIES 24
Frequent Visitor

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

Helper I

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.

Community Support

Hi @rosscortb,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Helper I

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?

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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:

Super User

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)))``````

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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)))```

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hey Ashish,

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

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

Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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))`

Post Patron

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

Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors