Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Last months Rankx

I am trying to calculate the movement of rank based on last month below is currently how i am calculating the rankx. What i am trying to do is basically calculate exactly the same but just look at last month;
 
Rank =
SWITCH (
TRUE (),
ISINSCOPE ( Sales[Product] ), RANKX ( ALLSELECTED (Sales[Product] ), [Total Points],, DESC, DENSE ),
ISINSCOPE ( Sales[store] ), RANKX ( ALLSELECTED ( Sales[store]), [Total Points],, DESC, DENSE ),
ISINSCOPE (Sales[Month Name] ), RANKX ( ALLSELECTED ( Sales[Month Name] ), [Total Points],, DESC, DENSE ),
ISINSCOPE ( ISales[User] ), RANKX ( ALLSELECTED (Sales[User] ), [Total Points],, DESC, DENSE )
)
Need Help
Show and Tell

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , using time intelligence create a previous month measure and have a rank on it

    last month Total Points = CALCULATE([Total Points],previousmonth('Date'[Date]))

     

    Create a rank on this measure

    othe example

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))
Next to next  month value =  CALCULATE(sum('table'[total hours value]),nextmonth(dateadd('Date'[Date],1,MONTH)))
previous to previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))
Last year same month value =  CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-12,MONTH)))

     

    Power BI — MTD
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

     

     

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi , Anonymous

    Could you please tell me whether your problem has been solved?
    If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

     

    Best Regards,
    Community Support Team _ Eason