I am trying to count the rows in a table that contain the previous INPUTED date (sometimes there are weekend dates, and sometimes there isn't).
A normal previous date function would work for Tuesday - Friday, but Monday would be off (Data sometimes has values for Saturday, sometimes not)
Anyone have an idea?
Please provide more detailed information to help use clarify your requirement.
Sample data, screenshots, calculate steps,...
|Date of Report Ran||9/18/2017|
|Units Started Previous Day||8||Widget1||9/16/2017|
|Date of Report Ran||9/19/2017|
|Units Started Previous Day||9||Widget1||9/18/2017|
For your scenario, I think you can try to find out the last date, then use it to filter and calculate the related records.
Sample measure= var checkdate=Today() var last_date=MAXX(FILTER(ALL('Table'),[Date]<checkdate),[Date]) return COUNTROWS(FILTER(ALL('Table'),[Date]=last_date))
Manually filtering a date for multiple tiles is not an option. I need to "hard code" the previous dates from production.
I would think it would be easier to tell MS BI to "Look at today, and find the first previous date"
My formula can used to find out the last previous date of current date.(it also works on discontinuous date range)
Comment: find out smaller date than current date, then get the max one of them.
Previous Date = MAXX(FILTER(ALL(Sheet4),[Date]<MAX(Sheet4[Date])),[Date])
I am trying to count the rows of the previous day, this is where I'm at so far.
Scheduled Starts Yesterday = CALCULATE(COUNTROWS(Table 1),MAXX(FILTER(ALL(Table1),TODAY()<MAX(Table1[SCH_STRT_DT])),TODAY() && Table1[SECT_NO]="1234" && Table1[SLS_MDL]<>"18C" && PKG_ORD[SLS_MDL]<>"9C" && Table1[SLS_MDL]<>"12C"))
But I am getting the error "Maxx has been used in a True/False expression that is used as a table tilter expression, This is not allowed"
I try to modify and reformat your formula, perhaps you can take a look on at below formula.
Scheduled Starts Yesterday = VAR filtered = FILTER ( ALL ( Table1 ), Table1[SECT_NO] = "1234" && Table1[SLS_MDL] <> "18C" && Table1[SLS_MDL] <> "12C" ) RETURN CALCULATE ( COUNTROWS ( Table1 ), filtered, Table1[SCH_STRT_DT] = MAXX ( FILTER ( filtered, [SCH_STRT_DT] < TODAY () ), [SCH_STRT_DT] ), PKG_ORD[SLS_MDL] <> "9C" )