Filter rows to show sales for Today/Week/Year

[jaidee]

I am making a dashbords, and want to show the sale for current day, week and year.

In Data View I cant get Table.SelectRows to work.

If I open the Query Editor, I can set my sales.date row to filter to for instance current day. But I am using this table for other things, so I need a new table.If I just duplicate the table in Query editor then it is a snapshot, and will not update with new rows (sales).

How to make a new table and how to filter correct. I am loading from a Mysql, so I need it to work after refreshes.

[MattAllington]

If I understand you correctly, I think you are going about this the wrong way.  The general approach is to use Power Query (get data, edit query) to load all the data you will need into power bi. Once it is all loaded, you should use the visualisation tools and power pivot to display the data you want to see. 

Do  you have a calendar table?

here are a couple of posts I wrote that will.provide the basics. 

http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

http://exceleratorbi.com.au/power-pivot-calendar-tables/

[jaidee]

Deleted

[jaidee]

Hi Matt.

Thanks a lot. 

I now a a calender table connected in where datei s connected to date in the order table.

It works and I can sort on day, week, quarter year and see sales. I now want to do a dasbord just showing current day. In visualization and filters I can set certain dates, but how to make it always to sort on curret day?

[MattAllington]

The easiest way to do is is to add a mirror date column in your calendar table. The row in this column should return "today" if it is today's date, and simply the date (in text format) if it is not today's date.  You can use a calc column for this with a formula like =if(Calendar[date]=today(),"Today",Calendar[date]). 

Then use use this new column in your slicer. 

[jaidee]

Deleted

[jaidee]

Update:

1)

In Data view on my Orders table i managed to insert a new colum with this code, wich is working:

IsInCurrentYear = IF(YEAR(NOW())=Orders[Year];1;0)

This is also:

IsInCurrentYear = IF(YEAR(NOW())=[Year];'Orders'[Year];0)

But they only work in the Orders table. If I use the Calendar Table, it will show, but it seems there is something wrong with the relationships, since 2016 dont have sale. But I think it is okay to use Orders table?

2)

Another thing:

Before I got the above to work, I just duplicated my order table in Query Editor, and then filtered by the Date Filters "This Week". Then I only see the orders from this week in a new table, and I could make more duplicates of the order table an filter od day and year.

But will this make my report to heavy when i refresh data? Or anything else that is wrong with this way of diung it versus the above?

[MattAllington]

Sorry, I'm not at my PC to check the formula. 

This was wrong

=if(Calendar[date]=today(),"Today",Calendar[date]).

The he issue is the first half returns text and the second returns date

please try this (untested)

=if(Calendar[date]=today(),"Today",format(Calendar[date],"did-mm-yy")).

[jaidee]

Thanks again.

Regarding the other question. Is it wrong to duplicate my Orders table many times in Query Editor? Will it slow down the connection?

[MattAllington · Accepted solution]

Well it is not how I would do it, but there is no single correct way - do what works for you. Extra tables will slow refresh, and extra large tables will slow it more. The best way to do what you want is to reference the tables - don't duplicate. This will be a lot faster. 

1. Reference the main table and create the year table 

2 reference the year table to create the month tabke

3 reference the month table to create the day table

you could also try ALWAYS referencing the main table with all 3.  They may then load in parallel - I'm not sure. 

I pi assume you want the year table for some year to date calc. You don't need this table as year to date is easy to calculate with the TOTALYTD formula. Same with totalmtd   The only hard thing is getting the filter to stick to e latest day after refresh

[trebgatte]

Have you considered the following? This allows you to show a range of dates as well as today's date and doesn't require a calendar table. The trick is to consider the date range as sets.

=Value.Compare([YourSalesDate], DateTime.LocalNow()) 

or

=Value.Compare(Date.Month([YourSalesDate]), Date.Month(DateTime.LocalNow())) 

If the value is 1, then this would show anything greater than today or this month. You would likely not have this case.

If the value is 0, then the sales for today or this month are shown.

If the value is -1, all sales through yesterday or last month are shown.

This could provide the ground work for some alternative views easily. 

Hope this helps!

Treb Gatte, Power BI Red Carpet Partner | Microsoft MVP | Twitter | Blog | Blog 2

[jaidee]

Tanks for your help. i am nearly there.

I tried the reference solution, but it loads the order table three times. It is ok, but I would like only one in the perfect world.

I figured out how to do it by year

In the Order table I edited query and inserted year, quarter, week and day.

When I go to the order table ans add this column it  creates a colums with "1" in all rows with this year.And then In page level filters I just sort on that table to show "1"

IsInCurrentYear = IF(YEAR(NOW())=Orders[Year];1;0)

But how to make the same with weeks, quarters and days.

IsInCurrentYear = IF(Week(NOW())=Orders[Week];1;0) is not an option for instance