Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aarikc17
Advocate I
Advocate I

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
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Example BI.jpg

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

 

I recieved the same error "A function 'MAXX' has been used in a True?False expression that is used as a table filter expression. This is not allowed."

 

Also, it might help to know that I am using Direct Query Mode.

HI @aarikc17,

 

I modify the formula to move this condition into filter function, maybe you can try it.

 

Scheduled Starts Yesterday =
VAR filtered =
    FILTER (
        ALL ( Table1 ),
        Table1[SECT_NO] = "1234"
            && Table1[SLS_MDL] <> "18C"
            && Table1[SLS_MDL] <> "12C"
    )
VAR nearest_date =
    MAXX ( FILTER ( filtered, [SCH_STRT_DT] < TODAY () ), [SCH_STRT_DT] )
RETURN
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER ( filtered, Table1[SCH_STRT_DT] = nearest_date ),
        PKG_ORD[SLS_MDL] <> "9C"
    )

 

I'm not so sure if this formula is fully supported in directquery mode.

 

Regards,

Xiaoxin Sheng

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

@v-shex-msft

 

You may be right but there has to be another way.

 

Right now I am getting the error "Function 'FILTER' is not supported in this context in DirectQuery mode".

 

What would that context be? I use the 'FILTER' function a lot in DireectQuery mode.

HI @aarikc17,

 

You can turn on the 'Allow unrestricted measures in DIrectQuery mode' option to use unsupported dax function.

5.PNG

 

6.PNG

 

Regards,

Xiaoxin Sheng

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

@v-shex-msft

 

I feel like we're getting closer, now the measure returns no data "(Blank)".

HI @aarikc17,

 

>>I feel like we're getting closer, now the measure returns no data "(Blank)".

If you can, please share a pbix file for further test.

 

Regards,

Xiaoxin Sheng

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

@v-shex-msft

 

What file type can I put the data in to support Direct Query Mode? Excel doesn't seem to work this way?

 

Power BI Desktop.jpg

Hi @aarikc17,

 

Please check below link to know the supported datasources.

Data sources supported by DirectQuery

 

BTW, below are limitation about directquery, I think you can take a look on it.

 

Limitations of DirectQuery

There are currently a few limitations to using DirectQuery:

  • All tables must come from a single database

  • If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor

  • Relationship filtering is limited to a single direction, rather than both directions (though it is possible to enable cross filtering in both directions for DirectQuery as a Preview feature). For multi-dimensional sources like SAP Business Warehouse, there are no relationships defined in the model

  • Time intelligence capabilities are not available in DirectQuery. For example, special treatment of date columns (year, quarter, month, day, so on) are not supported in DirectQuery mode.

  • By default, limitations are placed on DAX expressions allowed in measures; see the following paragraph (after this bulleted list) for more information

  • There is a 1 million row limit for returning data when using DirectQuery. This does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

 

Use DirectQuery in Power BI Desktop

 

Regards,

Xiaoxin Sheng

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

@v-shex-msft

 

There has to be another way.

 

I created a measure

 

Previous Date = CALCULATE(MAX(Table1[SCH_STRT_DT]),FILTER(Table1,Table1[SCH_STRT_DT]<TODAY())

 

and tried to use this as a filter input. I am now getting a value, just the wrong value.

 

Starts = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[SCH_STRT_DT]=[Previous Date] && Table1[SECT_NO]="04114" && Table1[SLS_MDL]<>"18C" && Table1SLS_MDL]<>"9C" && Table1[SLS_MDL]<>"12C"))

HI @aarikc17,

 

I'd like to suggest you use a variable to store the pervous date.

 

Starts = 
var Previous_Date = CALCULATE(MAX(Table1[SCH_STRT_DT]),FILTER(Table1,Table1[SCH_STRT_DT]<TODAY())
return
CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[SCH_STRT_DT]=Previous_Date && Table1[SECT_NO]="04114" && Table1[SLS_MDL]<>"18C" && Table1SLS_MDL]<>"9C" && Table1[SLS_MDL]<>"12C"))

 

 

Notice: if you drag a measure into a filter function as a parameter, sometimes it may get the wrong result.

 

Regards,

Xiaoxin Sheng

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

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
Advocate I
Advocate I

Any takers?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.