Data Differential

[Mathoosala]

Hello,

I have a report where I gather a list of names of computers that belong to Team A and store them in a CSV which is imported into PowerBI. Last week Team A had 25 computers. The csv has the name of the computers and I can use the count of computers to show that Team A had 25 computers. This week Team A is only going to have 15 computers. I run the same query I did last week and I put the csv in the same folder that contains last weeks csv.  When I select the date for last weeks report I see the count of 25 computers, when I change the date of the report to this week, I see that now there are only 15. How can I build a visual that will show me the 10 computers which are no longer here?

[amitchandak · Accepted solution]

@Mathoosala , have separate date oy year week(year week) table  (join on date/year week)

In that week/date table have rank column

Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

Then have measure like

This Week = CALCULATE(count('Table'[computer_id]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(count('Table'[computer_id]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

now lost computers =

countx(values(Table[computer_id]), if(not(isblank([last week]))  && isblank([this week]) , [Computer_id], blank()) )