Get number and values of Virtual Machines created

[willianmsantos]

Hello Community,

I have the following Visual.

It represents the amount of virtual machines running under my environment, which I can also use the slicer to set periods, good!

Among those values, I may have created and deleted machines from one day to another, when the line chart goes up it does not necessarily mean I only created machine, and well when the line goes down, it does not mean I only deleted machines.

Well, I'd like to create to add to chart bars which tell us by day how many machines were created and howmany machines were deleted. I would have to create a measure or a custom collumn in order to compares those differences between one day and another and translate it to numbers and visuals.

Could you please anybody help me with that issue?

I'd really appreciate your help!

Here is the complete table:
https://drive.google.com/file/d/0Bx1SM-BuvfI4aHVlSll4ZzhJNWc/view?usp=sharing

[v-shex-msft · Accepted solution]

Hi @willianmsantos,

You can try to use below sample to achieve your requirement:

Analysis Data = 
var _history=CALCULATETABLE(VALUES('Total VMs'[VM]),FILTER(ALL('Total VMs'),[DateEn]<EARLIER('Total VMs'[DateEn])))
var _current=CALCULATETABLE(VALUES('Total VMs'[VM]),FILTER(ALL('Total VMs'),[DateEn]=EARLIER('Total VMs'[DateEn])))
var devices_found=COUNTROWS(_current)
var devices_not_found=COUNTROWS(EXCEPT(_history,_current))+0
var new_devices=COUNTROWS(EXCEPT(_current,_history))+0
Return
"Total: "&devices_found&" , Remove: "&devices_not_found&" , New: "&new_devices

New VM = 
var _history=CALCULATETABLE(VALUES('Total VMs'[VM]),FILTER(ALL('Total VMs'),[DateEn]<EARLIER('Total VMs'[DateEn])))
var _current=CALCULATETABLE(VALUES('Total VMs'[VM]),FILTER(ALL('Total VMs'),[DateEn]=EARLIER('Total VMs'[DateEn])))
var devices_found=COUNTROWS(_current)
var devices_not_found=COUNTROWS(EXCEPT(_history,_current))+0
var new_devices=COUNTROWS(EXCEPT(_current,_history))+0
Return
new_devices

Remove VM = 
var _history=CALCULATETABLE(VALUES('Total VMs'[VM]),FILTER(ALL('Total VMs'),[DateEn]<EARLIER('Total VMs'[DateEn])))
var _current=CALCULATETABLE(VALUES('Total VMs'[VM]),FILTER(ALL('Total VMs'),[DateEn]=EARLIER('Total VMs'[DateEn])))
var devices_found=COUNTROWS(_current)
var devices_not_found=COUNTROWS(EXCEPT(_history,_current))+0
var new_devices=COUNTROWS(EXCEPT(_current,_history))+0
Return
devices_not_found

Total VM = 
var _history=CALCULATETABLE(VALUES('Total VMs'[VM]),FILTER(ALL('Total VMs'),[DateEn]<EARLIER('Total VMs'[DateEn])))
var _current=CALCULATETABLE(VALUES('Total VMs'[VM]),FILTER(ALL('Total VMs'),[DateEn]=EARLIER('Total VMs'[DateEn])))
var devices_found=COUNTROWS(_current)
var devices_not_found=COUNTROWS(EXCEPT(_history,_current))+0
var new_devices=COUNTROWS(EXCEPT(_current,_history))+0
Return
devices_found

Summary records:

Table = SUMMARIZE('Total VMs',[DateEn],[Analysis Data],[Total VM],[New VM],[Remove VM])

Build line chart based on above data.

Notice:

1. DateEn is used to convert the 'UK' date to 'US' date(on my side, 'UK' date not works)

2. You can use 'total' - 'remove' to get the current device amount.

Regards,

Xiaoxin Sheng

[willianmsantos]

Wow @v-shex-msft!

Thank you very much for all your effort and concern upon my issue, I will reproduce that in my environment. Thank you so much !

Will