cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aarikc17 Regular Visitor
Regular Visitor

Previous INPUTED Date Count

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?

19 REPLIES 19
aarikc17 Regular Visitor
Regular Visitor

Re: Previous INPUTED Date Count

Any takers?

Community Support Team
Community Support Team

Re: Previous INPUTED Date Count

Hi @aarikc17,

 

Please provide more detailed information to help use clarify your requirement.
For instance:
Sample data, screenshots, calculate steps,...

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
aarikc17 Regular Visitor
Regular Visitor

Re: Previous INPUTED Date Count

Date of Report Ran9/18/2017   
   UnitsStart Date
Units Started Previous Day8 Widget19/16/2017
   Widget29/16/2017
   Widget39/16/2017
   Widget49/16/2017
   Widget59/16/2017
   Widget69/16/2017
   Widget79/16/2017
   Widget89/16/2017
     
Date of Report Ran9/19/2017   
   UnitsStart Date
Units Started Previous Day9 Widget19/18/2017
   Widget29/18/2017
   Widget39/18/2017
   Widget49/18/2017
   Widget59/18/2017
   Widget69/18/2017
   Widget79/18/2017
   Widget89/18/2017
   Widget99/18/2017
aarikc17 Regular Visitor
Regular Visitor

Re: Previous INPUTED Date Count

Example BI.jpg

Community Support Team
Community Support Team

Re: Previous INPUTED Date Count

Hi @aarikc17,

 

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

 

Regards,

Xiaoxin sheng

 

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
aarikc17 Regular Visitor
Regular Visitor

Re: Previous INPUTED Date Count

@v-shex-msft

 

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"

Community Support Team
Community Support Team

Re: Previous INPUTED Date Count

HI @aarikc17,

 

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.

Measure:

Previous Date = MAXX(FILTER(ALL(Sheet4),[Date]<MAX(Sheet4[Date])),[Date]) 

5.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
aarikc17 Regular Visitor
Regular Visitor

Re: Previous INPUTED Date Count

@v-shex-msft

 

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"

Community Support Team
Community Support Team

Re: Previous INPUTED Date Count

Hi @aarikc17,

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |