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

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
Anonymous
Not applicable

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.

Ashish_Mathur
Super User
Super User

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

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.

 

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 others find it more quickly.

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/
Anonymous
Not applicable

Hi @Ashish_Mathur

 

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

You are welcome.


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

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.