cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Get the previous values of a specific date

All, 

I have a table which has Sales value with the various dates. The date will not be up to date (let's say it is not till Feb 2019 which is today's date). I need to find the following:

1. Current Month Sales  - As I mentioned the data is not up to date, I have used DAX to get the latest available data and calculated the SUM of sales 

LatestDate = CALCULATE(MAX(Sales[Month]), ALLEXCEPT(Sales, Sales[Month]))
IsLatest = IF(Max(Sales[Month]) = [LatestDate], "Yes", "No")

Here Month is the date column

 

2. Previous Month Sales  - Let's say I have only data till Dec 2018 and I have found it in step 1 using Latest Date and Is Latest DAX. Can any one help how to find the sum of slaes of this specific date available?

 

3. Previous Year Same Month - There are two scenarios here since the data is not up to date is not updated properly. 

Again I need to consider latest available date. Let's say it is Dec 2018. 

For calculating Previous Year Same Month - I need to check if Dec 2017 data is available then display the SUM of the sales, else I need to get the least date and do the math (sum of sales). Let's say the data starts from Jan 2018 only then I will display Jan 2018 data. 

 

Any help is greatly appreciated. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Get the previous values of a specific date

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


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

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Get the previous values of a specific date

Hi,

 

Share a dataset and show the expected result.


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

Re: Get the previous values of a specific date

Here you go:

Month          Sales

1-Oct-18      1000

12-Oct-18     4000

23-Oct-18     2345

4-Nov-18      4489

12-Nov-18    3939

15-Nov-18     494

30-Nov-18    3838

1-Dec-18      8339

8-Dec-18      3169

25-Dec-18    3097

So, here we don't have data up to current month so I have used MAX function to get the latest date available and calculated the Current Month Sales which is sum of December Month sales

Previous Month - I need to find all the sales of the previous month of the latest month available - here it is Nov 18 - Which will be sum of all November month data

Same Month Last Year sales - If Dec 17 data is available (since Dec 18 is the current month available) then display the sum of sales of Dec 17 data else show the least available data which is Oct -18 here in this example shown.

Super User
Super User

Re: Get the previous values of a specific date

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


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

View solution in original post

Community Support Team
Community Support Team

Re: Get the previous values of a specific date

 

Hi @Anonymous,

 

One sample for your reference. Please check the following steps as below.

 

1. Create a calculated table as below.

 

 

DATE = CALENDARAUTO()

 

 

2. Create the measures as below.

 

 

current = var _sele = SELECTEDVALUE('DATE'[Date])
return
CALCULATE(SUM(Table1[Sales]),FILTER(Table1,FORMAT('Table1'[Date],"yyyymm")=FORMAT(_sele,"yyyymm")))
Previous = var _sele = DATEADD('DATE'[Date],-1,MONTH)
return
CALCULATE(SUM(Table1[Sales]),FILTER(Table1,FORMAT('Table1'[Date],"yyyymm")=FORMAT(_sele,"yyyymm")))
Same Month Last Year sales = var _sele = DATEADD('DATE'[Date],-1,YEAR)
return
CALCULATE(SUM(Table1[Sales]),FILTER(Table1,FORMAT('Table1'[Date],"yyyymm")=FORMAT(_sele,"yyyymm")))

 3.PNG

 

 

Please check the pbix as attached. If it doesn't meet your requirement,kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.

 

Regards,

Frank

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

Re: Get the previous values of a specific date

Hi @v-frfei-msft,

 

Thank you for your time in sharing the PBI file. Actually I didn't mention that there should be no filters in the report and it has to pull the latest month.

Your solution is perfect for the one with filter option scenario. Thanks again.

Anonymous
Not applicable

Re: Get the previous values of a specific date

Hi @Ashish_Mathur

 

Thank you for the solution and it worked perfectly for my scenario. 

Super User
Super User

Re: Get the previous values of a specific date

You are welcome.


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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 407 members 3,805 guests
Please welcome our newest community members: