For the one day data i need to fetch from Yesterday 11pm to today 11pm

[Chinnu3]

I need one day data from yesterday 11pm to today 11pm. i need to add this filter in front end. how can I make this happened in front end as a filter.

Sales Date (Column name)

11-11-2021 11:50:40 PM

12-11-2021 10:30:00 PM

14-11-2021 10:10:40 PM

15-11-2021 10:40:40 PM

[Andrei_Cusnir]

Hello,

The use case scenario that you have described is not very clear at the moment. You have mentioned "filter" which means that you would like to filter the data in the dataset, but it is done after the data is already fetch from the source. However, in the title you have mentioned that you would like to "fetch" the data for the last 24 hours, which is an action that you can only do from the Data load editor or from the Data manager.

I have prepared for you a couple of various options that cover most of the specified use case scenarios and the dataset that I use is:

1. If you are interested in having a button that will create a filter based on the 24 hour timeframe that you have specified, then you can follow the approach:

1. Create a table with all the data
2. Create a button
3. Add an action with the following details:
4. Label: Action
5. Action: Select values matching search criteria
6. Field: Sales Date
7. Value: ='>=11/11/2021 11:50:00 PM<=11/12/2021 11:50:00 PM'
8. This expression will filter the data in the dataset for values grater and equal to the one that you want
9. You can modify this filter as needed to cover a more complicated use case scenario

This is the result that you get when the button is clicked:

2. If you would like to count the data based on the 24 our timeframe, then you can use a set analysis in your measure:

For example you can create a KPI chart with the expression Count( {<[Sales Date]={">=11/11/2021 11:50:00 PM<=11/12/2021 11:50:00 PM"}>} Value).

This expression counts the data Value only where the Sales Date is within the specified 24 hour timeframe. Again you can modify this expression as needed to cover a more complicated use case scenario.

3. Another use case scenario, is to "fetch" the data based on the 24 hour timeframe that you have specified:

For example you can use the following scrip in Data load editor:

LOAD
    "Sales Date",
    Value
FROM [lib://Folder/dataset.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE "Sales Date" >= '11/11/2021 11:50:00 PM' and "Sales Date" <= '11/12/2021 11:50:00 PM';

This script will only "fetch" the data from the source that is within the specified 24 hour timeframe.

I hope that this information was helpful. In case the issue was resolved, please mark the answer as solution to give it more visibility for other community members. Otherwise, if none of the points above have helped you resolve the issue, please elaborate in detail to which exactly is the use case scenario that you are trying to achieve. A screenshot of the issue will be helpful and you can also share with us, what you have already tried!

[Chinnu3]

Thank you soo much for your reply...

[Chinnu3]

I Have data as 

I need a filter in front end like which i select i need to see the data last 24hrs from yesterday 11pm to today 11pm. when ever i select this filter it should show yesterday's 11pm onwards today 11pm only.

If today date and time is 15-11-2021 11:30:20pm, If I select that filter it should show below data.

[Andrei_Cusnir · Accepted solution]

Hello,

Thank you for elaborating in detail to your use case scenario. With a combination of Variable input object and a Button object, I was able to implement something similar to what you need. You can take the logic behind the implementation and modify the expressions based on your needs. There could be other potential solutions to your use case scenario, however this is the only one that I was able to implement, that can get you as close as possible to your desired outcome. These are the steps that I have followed:

1. My new dataset looks like this:
2. 
3. I have created a new variable:
4. 
5. Then I have created a Variable Input object:
6. The configuration of the variable is:
7. 
8. After that, I have created a Button object with configuration:
9. Label: Select values
10. Action: Select values matching search criteria
11. Field: Report_Date
12. Value: 

='>=$(=Date(Date#('$(varSelectedDate)','D-M-YYYY h:mm:ss[.fff] TT')-1,'D-M-YYYY h:mm:ss[.fff] TT'))<=$(=Date(Date#('$(varSelectedDate)','D-M-YYYY h:mm:ss[.fff] TT'),'D-M-YYYY h:mm:ss[.fff] TT'))'

Allow me to break down the expression:

1. The combination of function Date() and Date#(), will convert the string value of the variable to a real date that you can set as a filter
2. The date format is D-M-YYYY h:mm:ss[.fff] TT
3. The general expression is >=DATE_A<=DATE_B
4. So DATE_A is calculated by adding -1 day to the converted date found in the variable. This means that no matter what date you select in the variable, it will go back 24 hours
5. DATE_B is just the converted string value found in the variable that the user has selected with the Variable Input object
6. So the entire expression takes the date that you have specified in the Variable Input, and filters the values found in the dataset so that you only select for the value that you have specified and 24 hours before.

Demonstration:

1. As you can see this is the entire view of the app:
2. 
3. I have specified the date 15-11-2021 10:23:30 PM in the Variable Input
4. NOTE: The value should be specified in the exact format as your dataset, otherwise the functions Date() and Date#() won't be able to properly parse the date format so it will return "-" (Null) and the filtering won't work.
5. Clicked on the button 
6. And I can see the values:
7. 
8. This is because those are the corresponding values for the 24 hour timeframe of the selected date minus 24 hours from that date.

This might not be the exact solution that you were looking for. However, as mentioned above, you can take the logic of how it works and modify it according to your needs. You can use the function Today() to get the current date and then subtract 24 hours or if you would like to make the Variable Input more reliable, so the user won't have to worry about specifying the exact value with the exact format, you can create multiple Variable Input objects with dropdown option to select Year, Month, Day separately and then construct the entire string in the expression.

I hope that this information was helpful!