Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Retrieving only last 7 days based on certain status

Hi Guys!

 

Im struggling a lot with the following:

I would like to retrieve data only for the last 7 days. Can anyone assist?

Below are examples I tried which do not lead to the desired result

 

Completed last 7 days = CALCULATE(DISTINCTCOUNT('table1'[ItemState]),FILTER('table1','table1'[ItemState]="Completed", 'table1'[ItemDateTime] >= TODAY()-7))

PowerBI here says that there are more than 2 arguments

Therefore, I added '&&'

 

Completed last 7 days = CALCULATE(DISTINCTCOUNT('table1'[ItemState]),FILTER('table1','table1'[ItemState]="Completed" && 'table1'[ItemDateTime] >= TODAY()-7)) This sadly does also not work.

 

COUNTX(FILTER(RELATEDTABLE('Table1'),'table1'[ItemState]="Completed" && ( TODAY () - 'table1'[ItemDateTime] ) < 7 , 1))

Need Help

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    Try with these measures.  This is assuming you are just looking for the count of items.

     

    CompletedItems = CALCULATE( COUNTROWS( Table1 ) , Table1[ItemState] = "Completed" )
    MaxCompletedDate = CALCULATE( MAX ( Table1[Date] ) , FILTER( ALL ( Table1 ) , Table1[ItemState] = "Completed" ) )
    CompletedLast7Days = 
CALCULATE( [CompletedItems] ,
    FILTER ( Table1 , 
        Table1[Date] <= [MaxCompletedDate] &&
        Table1[Date] >= [MaxCompletedDate] -7 ))

     

    If this isn't what you were looking for, please post a sample data set for us to work with.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      jdbuchanan71 Thank you for your reply, this actually gets me the following measure:

       

      Process Name - Completed Count last 7 days

      Process A:               50

      Process B:               30
      Process C:               20

       

      However, now I would need to multiply these numbers with a process specific variable. For example

      Process A = ((50 x 30) / 3600) / 40

      Process B = ((30 x 100) / 3600) / 40

      Process C = ((20 x 50) / 3600) / 40

       

      How would i go about doing this? If it was a Column, I could just create a new column saying ' Completed Count last 7 days * [worktime]. Since it is a measure, I am unsure how to get the result.

       

      Any ideas? Thanks!

       

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        7 years ago

        It looks like you are multiplying the completed count * a factor then dividing by the seconds in a work week.
        If you are always using seconds in a work week that will be 144000

         

        So now, if you have a table with the process and factor in it you can join the table to your data table and use something like

         

        FactorValue = SUM ( 'processes'[factor] )

         

        Factored Value = [Completed Count Last 7 Days] * [FactorValue] / 144000