Show top 10 assignments backlog trend and compare same top 10 with previous month open backlog data

[sowmya2553]

hi there,

I need some real help for the complax problem here

sub: I need to show top 10 assignment groups backlog trend for current month and compare same top 10 groups with previous month open backlog data. How is it possible?

In excel im doing it manually and below graph is achievable. Please check and let me know if this can be done in power BI

Thanks in advance.

[Icey]

Hi @sowmya2553 ,

Please share us some sample data with the same structure of your real data and make the data could be copied/used directly by us.

Best regards

Icey

[sowmya2553]

Hi There,

please let me know if you are able to access the sample files.

Please share if there is any solution.

[Icey]

Hi @sowmya2553 ,

I have no access to your file.

Best Regards,

Icey

[sowmya2553]

Please share your email ID to grant access on the drive link

[Icey]

Hi @sowmya2553 ,

Sorry, I have no access to share my Email ID. Please create some sample data, not real data, and share it publicly. 

Best Regards,

Icey

[sowmya2553]

Im sorry to say but this is real difficult task for me to create datasets here.

Its very complicated one.

Hence I have the demo worksheets which are already uploaded in drive.

I modified the permission levels on below link

https://drive.google.com/drive/folders/17m4QZzCzQQ6viUgzt95SHHAvUvaEATaC?usp=sharing

please check now if you can access and download.

[sowmya2553]

https://drive.google.com/drive/folders/17m4QZzCzQQ6viUgzt95SHHAvUvaEATaC?usp=sharing 

I have uploaded here the test files. Check if you can access

There are 3 files.

-> open backlog - Sep = think that is is current month and I will need to show top 10 groups along with age bucket

-> Open backlog Aug = as this is previous month, I need to consider same top 10 groups of Sep file and see how much backlog count was there for Aug month (for same top 10 groups of Sep file)

-> backlog graph = then I will prepare a combined graph to display both actual vs previous in one visual.

Please let me know how to acheive this in power BI.

Thank you.

If you cant access the link, please give me email ID, I will try to add.

[amitchandak]

@sowmya2553 , Not 100% sure, try like

measure

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

top 10 measure

Top 10 group MTD Sales = CALCULATE([MTD Sales ],TOPN(10,all(Table[Resolver Group]),[MTD Sales],DESC),VALUES(Table[Resolver Group]))
Top 10 group last MTD Sales = CALCULATE([last MTD Sales ],TOPN(10,all(Table[Resolver Group]),[last MTD Sales ],DESC),VALUES(Table[Resolver Group]))

Top 10 group last MTD Sales = CALCULATE([last MTD Sales ],TOPN(10,all(Table[Resolver Group]),[last MTD Sales ],DESC),VALUES(Table[Resolver Group]))

[sowmya2553]

Thanks for trying to help.

I have tried this and this doesn't work as expected 😞