Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Need help to write DAX measure

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) wi...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
mattbrice's avatar
mattbrice
Icon for Solution Sage rankSolution Sage
9 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

mattbrice, Greg_Deckler can you please give any suggestions..

I ll be so thankful to you

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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