Forum Discussion

Mathoosala's avatar
Mathoosala
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

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...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    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()) )

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

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()) )