cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Talesfg
New Member

Return Top row result

I have a table in fact that I have a calculation of a KPI, in the DAX view I need to return the result of the previous axis as shown in the image below from a dimension table.

 

Example, 2021 Q3 the KPI2 line returns the result of 2021 Q2

 

Talesfg_0-1637256805043.png

 

I used a form of rank to find the previous value with reference to rank -1, but I couldn't find any solution.

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Talesfg ;

You can use the Label_ period column directly, without the aid of rank. Please try it.

KPI 2 =
CALCULATE (
    SUM ( [KPI] ),
    FILTER (
        ALL ( 'Table' ),
        [label_ period]
            = MAXX (
                FILTER ( ALL ( 'Table' ), [label_ period] < MAX ( 'Table'[label_ period] ) ),
                [label_ period]
            )
    )
)

 The final output is shown below:

vyalanwumsft_0-1637560927210.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

Hi, @Talesfg ;

You can use the Label_ period column directly, without the aid of rank. Please try it.

KPI 2 =
CALCULATE (
    SUM ( [KPI] ),
    FILTER (
        ALL ( 'Table' ),
        [label_ period]
            = MAXX (
                FILTER ( ALL ( 'Table' ), [label_ period] < MAX ( 'Table'[label_ period] ) ),
                [label_ period]
            )
    )
)

 The final output is shown below:

vyalanwumsft_0-1637560927210.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@v-yalanwu-msft,

Thank you very much, it worked perfectly.

Ps. Talesfg

amitchandak
Super User
Super User

@Talesfg , you need to create a dense rank on period label  or order KPI and prefer that to be in separate column along with base column, joined to this table

 

example

new column

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

 

Measure 
This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

 

 

 

The approach which we follow on week or custom months

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak ,

 

Thank you very much, this solution also worked.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.