Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Previous Day for Shift Timeframes Saturday=Friday

Hi guys,

 

i need help with a function where i calculate a measure for production.

 

ScrapPercentage Yesterday = 

DIVIDE(CALCULATE(SUM(TicketTable[ScrapQuantity]),PREVIOUSDAY(TicketTable[ShiftDate])),CALCULATE(SUM(OEE_1[Menge]),PREVIOUSDAY(OEE_1[SchichtDatum]),OEE_1[Art]="R"),0)+0
 
The problem is that the previousdayfunction doesnt work because the shiftdate (its an SQL Table) over the weekend will be counted to friday. How can i correctly calculate the value then? Because i need to skip 1 day.
 
For example today 7.2. i need the "previousday" value of the the 4.2
Thats the problem for mondays..
 
 
Appreciate your help!
BR
Fabian
 
 
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , You can create rank on working days

    new columns in date table

    Work Day = if(WEEKDAY([Date],2)>=6,0,1)
    Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
    Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
    Work Date cont Rank = RANKX(ALL('Date'),[Work Date],,ASC,Dense)

     

     

    measures

    This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
    Last work day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You can create rank on working days

    new columns in date table

    Work Day = if(WEEKDAY([Date],2)>=6,0,1)
    Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
    Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
    Work Date cont Rank = RANKX(ALL('Date'),[Work Date],,ASC,Dense)

     

     

    measures

    This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
    Last work day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    What does the rank function do in this case? i try to understand everything. And why you using for this day not just the formula today() ?