Display last day's data from a month

[Yakimo]

I have several years data in a SQL table, and want to select and display only every last day of every month's data

Date	Value
…	…
29-Jan-16
30-Jan-16	11
31-Jan-16	12
1-Feb-16	13
…	…
27-Feb-16	15
28-Feb-16	16
29-Feb-16	17

This is what I want to display. Note that I have 3 years data, i.e. more that 500 records

How can I achieve that?

Desired result data:

Month	Value
…	…
1-Jan-16	12
29-Feb-16	17

Thanks,

Yakimo

[Meneghino]

Hi @Yakimo

From this post you see that <= expressions with dates only work if your data type is datetimeoffset, so we will try to get a solution that works with = only.

If you had 500 rows or less you could use the GroupBy function straight away, but since you have more then you need to filter the data.

Since you have >500 rows, I would suggest you simply create a table in memory of all end of month dates and then lookup the value in your original table. To create the table (EndOfMonths_2014_2017) just create a single column table in Excel (call the column EndOfMonth) and then load it into PowerApps as static data.

Then your expression would be something like this:

Filter(AddColumns(EndOfMonths_2014_2017, "EndOfMonthValue", LookUp(MySourceTable, MySourceDate = EndOfMonth, MyValue), !IsBlank(EndOfMonthValue))

Please let me know how you get on.

[CarlosFigueira]

You can also use some date manipulation to create a collection with the last days of the months dynamically, so that you wouldn't need to hard-code all those values. Not as simple as @Meneghino's solution, but this way you'd have a "rolling window" of the last days of the month that wouldn't need to be updated.

Let's build the formula by parts. The rolling window I'll create will have the last day of the month for the past 3 years, but you can update it easier:

AddColumns(
    [-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35],
    "FirstDay",
    DateAdd(
        Date(Year(Today()), Month(Today()), 1),
        -Value
        Months))

Now we can subtract 1 day from each of those values, to create a table containing the last days of the (previous) months.

AddColumns(
    [-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35],
    "LastDay",
    DateAdd(
        DateAdd(
            Date(Year(Today()), Month(Today()), 1),
            -Value
            Months),
        -1,
        Days))

Finally, date comparison can be tricky sometimes, since some values are stored as simple dates, others as date/times, so if you have this problem, you can normalize the table of last days of the month as strings (I needed to do that to make this scenario work for an Excel table)

AddColumns(
    [-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35],
    "LastDay",
    Text(
        DateAdd(
            DateAdd(
                Date(Year(Today()), Month(Today()), 1),
                -Value
                Months),
            -1,
            Days),
        DateTimeFormat.ShortDate))

Finally, putting it together with the collection I want to filter, here's the formula I used in the Items property of a gallery to display only the items from the last days of the month:

Filter(
    OrdersTable,
    Text(OrderDate, DateTimeFormat.ShortDate) in
        AddColumns(
            [-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35],
            "LastDay",
            Text(
                DateAdd(
                    DateAdd(
                        Date(Year(Today()), Month(Today()), 1),
                        -Value
                        Months),
                    -1,
                    Days),
                DateTimeFormat.ShortDate)))

As I mentioned in the beginning, this is more complex than the other solution; but with it you won't have to update the app in a few months (or next year). Your scenario will determine which is the best alternative.

[Yakimo]

Hi @Meneghino

Thanks for the help. Unfortunately I couldn't get it working as I wish.

I did the following: created XLS file with the last dates of the month and added that datasource in my app.

Later I loaded it in a collection to work with collection, not on data source (even though with data source results is exactly the same. Do not know why you suggested to load in memory)

Later using the filter formula you provided returns no any result ([dbo].[SADaily].Date comparing to LastD_M)

Filter(AddColumns(LastDM, "SAYearlyN", LookUp('[dbo].[SADaily]', 
Date= LastD_M, SAYearly)), !IsBlank(SAYearlyN))

If I convert both dates to text, I get back results, but as you can expect - only those that match the first 500 records from my SQL Data table. So I cannot come to year 2017 at all.

Filter(AddColumns(EndOfMonths_2014_2017, "SAYearlyN", LookUp('[dbo].[SADaily]', 
Text(Date,ShortDate) = Text(LastD_M,ShortDate), SAYearly)), !IsBlank(SAYearlyN))

Any suggestions how to proceed?

There is one more thing. Actually I would like to show the last date of the month (which is what I get from those defined dates), but also for the current month, that is not completed, I need to show the last available data. (I have running total which resets to the end of every month)

I.e. 31-jan-2017, 28-feb-2017, 31-march-2017, 12-apr-2017 (which is today, the last data for the month is today)

This in SQL terms is GroupBy date(year,month) and get the max date

But since here we cannot use views (sql) I do not know how to do that

Any ideas?

Regards,

Yakimo

[Yakimo]

Hi @CarlosFigueira

I am impressed! Great technic to generate last day of the month.

AddColumns (...) works great - I added the formula in Items property, and saw the dates - correct the last of the month.

But your last formula cannot get it working, with the filter - I got two error messages

"In" (which was underline blue) says: 

Suggestion: Part of the formula cannot be evaluated remotely. Right side of “in” operator is not a column name

And the rest in underline red and says: Invalid schema. Expected a one-column table

Filter(
    '[dbo].[SADaily]',
    Text(Date, DateTimeFormat.ShortDate) in AddColumns(
            [-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35],
            "LastDay",
            Text(
                DateAdd(
                    DateAdd(
                        Date(Year(Today()), Month(Today()), 1),
                        -Value,
                        Months),
                    -1,
                    Days),
                DateTimeFormat.ShortDate)))
        )

Next one

 

And even we get the formula above working, I suspect that because of Text conversion of Dates, the filter will work only on the first 500 records from SADaily table (see my reply to @Meneghino in the previous post), and I cannot reach even year 2017

Any suggestions how to proceed?

There is one more thing. Actually I would like to show the last date of the month (which is what I get from your formulas), but also for the current month, that is not completed, I need to show the last available data. (I have running total which resets to the end of every month)

I.e. 31-jan-2017, 28-feb-2017, 31-march-2017, 12-apr-2017 (which is today, the last data for the month is today)

This in SQL terms is GroupBy date(year,month) and get the max date

But since here we cannot use views (sql) I do not know how to do that

Any ideas?

Regards,

Yakimo

[Meneghino]

Hi @Yakimo

I have given up for the moment on using dates in PowerApps as there are many bugs.  On new projects I store dates in an integer column in SQL Server with value yyyymmdd, this way I can use the > and < operators for filtering and it will be delegated.  On existing projects I add a calculated column to the SQL Server table of integer type with the same value of year*10000+month*100+day, which also works.

I hope they solve and clarify the issues with dates soon, there sure are enough posts on this.

[JhonatasGois]

Deleted