create report

[Ramole]

Hi 

i am trying to generate a weekly monthly annual report screen within powerApps can you help please here is my SharePoint structure.

CollectonDelivery      Yes/No Item

CompanyName        Lookup Item

Order date              Date and Time Item

Shipping Method  Choice Item

TrackingNumber  Single line of text

TotalPieces          Number
Weight                Single line of text
Length                Single line of text
DeclaredValue    Currency
CODAmount      Currency
CODType            Choice
FreightCharges  Currency
Width                Single line of text
Height              Single line of text

Thanks 

[eka24]

Have you tried using a Gallery

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[Ramole]

Hi @eka24 

I am looking where to start can you help example formulas something i can click like weekly monthly annually so i can get the report 

Thanks  

[eka24]

I'm order to make it simple, show a sample data (example in Excel) and Also what you expect ( also in Excel)

The screenshot of what you expect Will help develop it in Powerapps. Otherwise it looks too general.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[Ramole]

Hi @eka24 

Here is the screenshot 

Thanks

 

[v-yutliu-msft]

Hi @Ramole ,

Since powerapps table is not exactly same as excel table, you can not get the same table like your pic directly.

Do you want to list data monthly/weekly?

Could you tell me which day is your start day? I assume as Sunday.

If so, I suggest you use a drop down , a data table and 2 buttons

I've made a similar test for your reference:

1)the drop down is for selecting Order date

set the drop down's Items:

Distinct(listname,'Order date')
//replace with your listname  

Then the drop down will list all the order date.

2)set button1's OnSelect:

Set(var,"monthly")

set button2's OnSelect:

Set(var,"weekly")

If you click button1, the data table will display data that belongs to the same month based on the date that you select in drop down.

If you click button2, the data table will display data that belongs to the same week based on the date that you select in drop down.

3)set the data table's Items:

(data table only display simple data type by default, for lookup field and choice field, you need to use AddColumns function.)

If(
IsBlank(var),
AddColumns(list,"CompanyName1",CompanyName.Value,"Shipping Method1",'Shipping Method'.Value),
var="monthly",
Filter(
   AddColumns(list,"CompanyName1",CompanyName.Value,"Shipping Method1",'Shipping Method'.Value),
   Month('Order date')=Month(DateTimeValue(Text(Dropdown1.Selected.Result)))
       ),
var="weekly",
Filter(
   AddColumns(list,"CompanyName1",CompanyName.Value,"Shipping Method1",'Shipping Method'.Value),
   'Order date'>=DateAdd(DateTimeValue(Text(Dropdown2.Selected.Result)),-Weekday(DateTimeValue(Text(Dropdown2.Selected.Result)))+1),
   'Order date'<=DateAdd(DateTimeValue(Text(Dropdown2.Selected.Result)),7-Weekday(DateTimeValue(Text(Dropdown2.Selected.Result))))
       )
)

 //when you not click any button, you will get all the data.

If you click monthly button, you will filter data based on month.

If you click weekly button, you will filter data based on week.

Best regards,

[Ramole]

Hi @v-yutliu-msft 

Thanks, what if i want to add yearly and fortnight on the report please 

Thanks 

[v-yutliu-msft · Accepted solution]

Hi @Ramole ,

Similar principle.

You could use Year() function to get the date value's year. You could use Month() function to get the date value's Month.

"fortnight on the report" means within two weeks before the selected date? You just need to make a little change on the weekly part.

For example: (selecteddate-14~selecteddate)

'Order date'>=DateAdd(DateTimeValue(Text(Dropdown2.Selected.Result)),-14)
   'Order date'=<DateTimeValue(Text(Dropdown2.Selected.Result))

To sum up, two more buttons.

Yearly's OnSelect:

Set(var,"yearly")

fortnight' s OnSelect:

Set(var,"fortnight")

The datatable's Items:

If(
IsBlank(var),
AddColumns(list,"CompanyName1",CompanyName.Value,"Shipping Method1",'Shipping Method'.Value),
var="monthly",
Filter(
   AddColumns(list,"CompanyName1",CompanyName.Value,"Shipping Method1",'Shipping Method'.Value),
   Month('Order date')=Month(DateTimeValue(Text(Dropdown1.Selected.Result)))
       ),
var="weekly",
Filter(
   AddColumns(list,"CompanyName1",CompanyName.Value,"Shipping Method1",'Shipping Method'.Value),
   'Order date'>=DateAdd(DateTimeValue(Text(Dropdown2.Selected.Result)),-Weekday(DateTimeValue(Text(Dropdown2.Selected.Result)))+1),
   'Order date'<=DateAdd(DateTimeValue(Text(Dropdown2.Selected.Result)),7-Weekday(DateTimeValue(Text(Dropdown2.Selected.Result))))
       )
var="yearly",
Filter(
   AddColumns(list,"CompanyName1",CompanyName.Value,"Shipping Method1",'Shipping Method'.Value),
  Year('Order date')=Year(DateTimeValue(Text(Dropdown1.Selected.Result)))
       ),
var="fortnight",
Filter(
   AddColumns(list,"CompanyName1",CompanyName.Value,"Shipping Method1",'Shipping Method'.Value),
  'Order date'>=DateAdd(DateTimeValue(Text(Dropdown2.Selected.Result)),-14)
   'Order date'=<DateTimeValue(Text(Dropdown2.Selected.Result))
       )
)

The key problem is now to calculate based on date.

These functions may help you:

1)Weekday, month,year function:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-datetime-parts

2)DateAdd:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-dateadd-datediff

Best regards,