Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I am a newbie to PowerBI and DAX.
I need your valueable suggestions and triks to solve the below query.
I would like to compare the current month(May-2017) Sales value(40) with previous month(Apr-2017) sales value(23).
And accoring to the comarision i would like to add another column which shows message as less than or greater than previous month.
But if previous month sales value is not avaible then it should comapre with the previous month sales value.
Like as above image the previous month value is not avaibel then I would like to comapre with THE previous month sales value(Mar-2017).
Please suggest me, it will be so helpful for me.
Thanks,
Mohan V
If you have a separate calendar table, this is how i would approach the problem:
Total Sales = Sum ( Table[Sales] )
and then
= VAR Sales_this_Month = [Total Sales] VAR Month_Last_Sales = CALCULATE ( LASTNONBLANK ( Calendar[Date], [Total Sales] ), FILTER ( ALL ( Calendar ), Calendar[Date] <= ENDOFMONTH ( PREVIOUSMONTH ( MAX ( Calendar[Date] ) ) ) ) ) VAR Sales_Previous_Month = CALCULATE ( [Total Sales], PARALLELPERIOD ( Month_Last_Sales, 0, MONTH ) ) RETURN Sales_this_Month - Sales_Previous_Month
This figures out the last date you had sales, no matter how many months prior. May be another option to try.
Thanks for the reply @mattbrice.
I did tried what you have suggested.
But i got struck at Here table name is Actual, and it is having columns as Date(Perfect date format), Actual( i.e Sales )
Please dont hesitate to correct me.
Well same here. That's what i get for freehanding Dax without testing. I look at it closer tomorrow if I have time. Sorry for the false lead.
@mattbrice, @Greg_Deckler can you please give any suggestions..
I ll be so thankful to you
Hi @Anonymous,
You can refer to below steps if it suitable for your requirement:
1. Add calcualte column to convert "month year" column to date.
Date = DATEVALUE([Year Month])
2. Add calculate column to compare with previoust record.
Compare with Prev = var previous=LOOKUPVALUE('Table 2'[Total],'Table 2'[Date],MAXX(FILTER(ALL('Table 2'),'Table 2'[Date]<EARLIER('Table 2'[Date])),[Date])) return IF([Total]>previous,"greater","less")
3. Add a calculated column to show the compare result.
Result = var prev=MAXX(FILTER(ALL('Table 2'),'Table 2'[Date]<EARLIER('Table 2'[Date])),[Date]) var prev2=MAXX(FILTER(ALL('Table 2'),'Table 2'[Date]<prev),[Date]) return IF([Compare with Prev]="Less",LOOKUPVALUE('Table 2'[Total],'Table 2'[Date],prev),LOOKUPVALUE('Table 2'[Total],'Table 2'[Date],prev2) )
Regards,
Xiaoxin Sheng
If you had a column like:
YearMonth = VALUE(CONCATENATE(YEAR([Month]),MONTH([Month])))
Then you should be able to do something like:
var CurrentYearMonth = VALUE(CONCATENATE(YEAR([TODAY]),MONTH([TODAY]))) IF(ISNULL(CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-1))), CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth)) - CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-2)), CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth)) - CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-1)) )
A similar formula doing a comparison would give you an indicator.
Wow that was huge @Greg_Deckler.
I didnt expected that.
But while I am trying this i got some issues here.its giving me a wrong result then i changed it into
But then When i tried the next one, theres lots of errors.
thanks for the quick reply.
Its really huge for me.
Sorry, that's what I get when trying to write DAX without testing.
Try this:
MyMeasure = var CurrentYearMonth = VALUE(CONCATENATE(YEAR(TODAY()),MONTH(TODAY()))) RETURN IF(ISBLANK(CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-1))), CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth)) - CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-2)), CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth)) - CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-1)) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |