Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi There,
I tried many solution that I can find on desktop reserch, but still unsuccessful to create DAX to get the number of last month, which are dynamic and follow from date filter. The details here.
Background:
I have a data table follow the picture below.
Result:
Would like to create a measure to show the number of last month total sales, can be relate with Date.
If Date filter is selected at before 9 May 2024, result should return:
But, if Date filter is selected at before 30 April 2024, result should return:
Question:
How to write a DAX to create a measure to get a number of "Last month total sales" (in the red box) and "This month total sales" (in the blue box)?
Recheck result:
Total sales of each month following picture below.
Sincerely,
Lolin Gene
Solved! Go to Solution.
Hi,@lolingeneHello, @johnbasha33and @amitchandak ,thanks for your concern about this issue.
Your solutions are great!
And I would like to share some additional solutions below.
Based on the fact that you used the date filter, I judged that the data type of the Date column you used was a date type and not a text type, so I did the following test according to the date type data type
Here's the test data I created(I used two ways of writing and they both worked fine)
This month total sales =
VAR selectMonth=MONTH(MAX('All_Sales'[Date]))
VAR selectYear =YEAR(MAX(All_Sales[Date]))
RETURN
CALCULATE(SUM(All_Sales[Total sales]),FILTER(ALL('All_Sales'),MONTH('All_Sales'[Date])=selectMonth &&
YEAR('All_Sales'[Date])=selectYear))
Last month total sales =
VAR SelectDate =MAX('All_Sales'[Date])
VAR LastMonthEnd=EOMONTH(MAX('All_Sales'[Date]),-1)
VAR LastMonthStart=EOMONTH(MAX('All_Sales'[Date]),-2)+1
RETURN
CALCULATE(SUM(All_Sales[Total sales]),FILTER(ALL('All_Sales'),'All_Sales'[Date]>=LastMonthStart&&'All_Sales'[Date]<=LastMonthEnd))
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@lolingene
Here is the test pbix.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,@lolingeneHello, @johnbasha33and @amitchandak ,thanks for your concern about this issue.
Your solutions are great!
And I would like to share some additional solutions below.
Based on the fact that you used the date filter, I judged that the data type of the Date column you used was a date type and not a text type, so I did the following test according to the date type data type
Here's the test data I created(I used two ways of writing and they both worked fine)
This month total sales =
VAR selectMonth=MONTH(MAX('All_Sales'[Date]))
VAR selectYear =YEAR(MAX(All_Sales[Date]))
RETURN
CALCULATE(SUM(All_Sales[Total sales]),FILTER(ALL('All_Sales'),MONTH('All_Sales'[Date])=selectMonth &&
YEAR('All_Sales'[Date])=selectYear))
Last month total sales =
VAR SelectDate =MAX('All_Sales'[Date])
VAR LastMonthEnd=EOMONTH(MAX('All_Sales'[Date]),-1)
VAR LastMonthStart=EOMONTH(MAX('All_Sales'[Date]),-2)+1
RETURN
CALCULATE(SUM(All_Sales[Total sales]),FILTER(ALL('All_Sales'),'All_Sales'[Date]>=LastMonthStart&&'All_Sales'[Date]<=LastMonthEnd))
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@lolingene
try the below
Last Month Sales =
VAR MaxDate = MAX('YourTable'[Date])
VAR LastMonthStartDate = DATEADD(MAX('YourTable'[Date]), -1, MONTH)
VAR LastMonthEndDate = EOMONTH(LastMonthStartDate)
RETURN
CALCULATE(
SUM('YourTable'[Sales]),
'YourTable'[Date] >= LastMonthStartDate && 'YourTable'[Date] <= LastMonthEndDate
)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Hi @johnbasha33 PREVIEW Thank you for your suggestion, I have tried your solution but found the error like below. Do you have any recommend to fix this?
Error: Too few arguments were passed to the EOMONTH function. The minimum argument count for the function is 2.
@lolingene , for data you can have measures like
This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
for name , try measure like
This Month Name =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
return
format(_max, "MMM-YYYY")
Last Month Name =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-1)
return
format(_max, "MMM-YYYY")
Hi amitchandak,
Thank you for your response, but seem like your DAX can't work with 'date' filter. If I'm filtered 'date' before 31 Mar 2024 The last month number will show blank.
So do you have any suggesttion to fix this issue, to show the last month number even date filter changed.
@lolingene you can edit the interaction and turn it off for the visual, so when you select date last month will still displa
User | Count |
---|---|
79 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
98 | |
89 | |
82 | |
61 |