Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
Hi,
Share a dataset and show the expected result.
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")))
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
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.