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

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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. :smileyhappy:

  • 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