Forum Discussion

SiddhantDb's avatar
SiddhantDb
Frequent Visitor
3 years ago

Last quarter product

 

Hi All I have a requirement,
i wanted to find out the top 5 Products for the last quarter

Elaboartion : in dashboard have three slicers 
1 : MTD (Single selection feature)
2: Year (Single selection feature)
3: Month(Single selection feature)

Lets assume,
1 : MTD(Selected value from slicer)
2 : 2022 (Selected year  from slicer )
3 :  Dec (Selected month  from slicer)
if a user selected above criteria MTD(Selected value from slicer) & 2022 (Selected year  from slicer ) & Dec (Selected month  from slicer)

Output user wanted to see for the last quarter, here the slicer is selected decemeber 2022 so i wanted to findout the top 5 products who sells top in last quarter(July, August, and September (Q3)) for 2022 that should be the y axis in field in power bi

and x axis = i want to find the last month sales values , if user selected december 2022 so the last month should novemeber month sales 



Kindly help to achieve this logic


Thanks
Sid

Need Help
Show and Tell

1 Reply

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

    SiddhantDb ,

     

    Based on what I got, Create a last qtr measure first

     

    example

    Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

    or

    Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))

    or Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

     

    Then have TOP like

     

    CALCULATE([Last QUARTER Sales],TOPN(5,all(Table[Seller]),[Last QUARTER Sales],DESC),VALUES(Table[Seller]))

     

     

    any other measure based on same top 5

     

    CALCULATE([QUARTER Sales],TOPN(5,all(Table[Seller]),[Last QUARTER Sales],DESC),VALUES(Table[Seller]))

     

    You can use measure with allexcept in case you need topn on sub total

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

    TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448