Forum Discussion
Data Differential
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?
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()) )
1 Reply
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()) )