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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 358 members 4,218 guests
Please welcome our newest community members: