Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rosscortb
Post Patron
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

 

PREVIOUS.PNG

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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

ppp.PNG

 

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.

View solution in original post

24 REPLIES 24
NadaFathy193
New Member

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

Mark_Berry
Helper I
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.

v-frfei-msft
Community Support
Community Support

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

ppp.PNG

 

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.

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?

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:
 
sidvix917_0-1697760540964.png

 



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
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

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

sidvix917_0-1698019384148.png

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.

sidvix917_1-1698019886246.png

 



  

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hey Ashish,

 

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

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

 

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1698042442087.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

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 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.