Current Period plus year to date measure

[ob1ie2000]

I am new to Power BI.  I currently create a weekly report for management using an Access database connected to Excel to give the final report.  I would like to change this to Power BI and I have started by connecting Power BI to the query in Access that does my calculations.  My first task is to replicate the fields I get with Access/Excel, but I am having trouble with the filters.

Lets assume our sales have just completed week 9, 2017.  Therefore I need to see:

Sales Value for week 9, 2017

Sales Value year to date week 9, 2017

Sales Value for week 9, 2016

Sales Value year to date week 9 2016

The problem is the filters in BI seem to only allow me to run one filter per report or page, but I need all these values and I need to be able to compare and run analysis on them.  I thought I could use a parameter and pass it to a Measure, but I can't seem to do it.  Can someone advise how they would do it?

[v-qiuyu-msft]

---

The problem is the filters in BI seem to only allow me to run one filter per report or page, but I need all these values and I need to be able to compare and run analysis on them.  I thought I could use a parameter and pass it to a Measure, but I can't seem to do it.  Can someone advise how they would do it?

---

Hi @ob1ie2000,

We can add one or more filter conditions to the Visual/ Page/ Report Level filter, see: Take a tour of the report Filters pane. Also we can add more than one Slicer visuals into the report to filter data. Query parameter can't be passed in the DAX currently.

In your scenario, you can consider create measures as suggested by the @mattbrice. If you still have problem with the report, you can share some sample data and images about desired results for our analysis.

Best Regards,
Qiuyun Yu

[ob1ie2000]

Thanks very much for the help.  After I posted I did some more investigation and came to the conclusion that I would need to use the date intelligence to get the reports I needed, but since I have started to go in a different direction.  It may help to understand the business I work in to understand the problems.

I’m an accountant/business analyst in a dairy.  The system we use although good from the milk collection from farmers, it is poor on sales information.  Sales are broken into two sections – bulk sales of milk/butter, etc. to customers for further processing, which would be typical of other businesses.  However, retail sales are very different.  Retail sales are delivered through agents.  These agents have their own customers as well as delivering to our customers.  It results in us invoicing the agent when they collect, then invoicing the customer at delivery and crediting the agent at the weekend reconciliation.  The system handles all of this automatically, but it means that to understand the sales you need to look at the sales over a week.  I should also say we use a 4/4/5 calendar so standard date intelligence doesn’t work.

The system has a BI, but it requires a person with a bit of skill to manipulate and the system doesn’t have a great level of masterdata to enrich the reports.  So currently I run a weekly sales report with customer number, product, quantity, value, etc. and import it into Access.  I have considered ODBC, but sometimes the information needs to be sanitised and this method gives me a bit more control.  In Access, I have masterdata – Product groups, customer groups, etc.  The end result is a query.  In Excel, I have a pivot table linked to the query.  This feeds a weekly summary report using SUMIFS and data validation.  For more information I have additional pivots.  The problem is that even though this is better information that was available it still has its limitations.  I want something that is more visual and easier to distribute to people on and off site, hence Power BI.

The logic of Power BI is confused me to begin with.  When I think of filters I think of Excel filters.  If I filter week 201709 sales I only see that information, so I thought a report or page filter would do the same.  The fact that you can write a measure that can look back and summarise a previous period that was essentially filtered out threw me.

That’s the background.  To get results now this is what I am doing:

Sales this period and year to date:

SalesPTY = SUM(Sales[ValuePTY])

SalesYTD = CALCULATE([SalesPTY],FILTER(ALL('Calendar'),'Calendar'[Year] =MAX('Calendar'[Year]) && 'Calendar'[Weeks] <= MAX('Calendar'[Weeks])))  

Sales this period last year and last year, year to date:

SalesPLY = CALCULATE([SalesPTY],FILTER(ALL('Calendar'),'Calendar'[Year] =MAX('Calendar'[Year])-1 && 'Calendar'[Weeks]=MAX('Calendar'[Weeks])))

SalesPYTD = CALCULATE([SalesPTY],FILTER(ALL('Calendar'),'Calendar'[Year] =MAX('Calendar'[Year])-1 && 'Calendar'[Weeks] <= MAX('Calendar'[Weeks])))

These are working in a matrix report, but not a chart.  I need a chart that will show the sales for each period.  For the current year I can just use the sales from my report, but for the previous year I have had to use the previous year to date formula less the year to date minus 1 week.

ValuePLY = CALCULATE([SalesPTY],FILTER(ALL('Calendar'),'Calendar'[Year] =MAX('Calendar'[Year])-1 && 'Calendar'[Weeks] <= MAX('Calendar'[Weeks]))) -CALCULATE([SalesPTY],FILTER(ALL('Calendar'),'Calendar'[Year] =MAX('Calendar'[Year])-1 && 'Calendar'[Weeks] <= MAX('Calendar'[Weeks])-1))

Finally, I appreciate what you are saying that I can filter and create reports using BI, but I am not the end customer.  I am trying to create a dashboard for management for each segment of the business with a parameter to allow them to select the week in question they want to look at.  Finally I am hoping I can publish the final report/dashboard to the web (including the parameter) so management off-site can access and interrogate the information without all requests coming through me.

I’m at the point now that I have seen some and see the power of it, but I have loads of questions and I’m not sure if my final plan is possible.

[mattbrice]

Power BI / Dax are all about manipulating the filter context that values are evaluated under.  Assuming you have a standard best practice Calendar table related to sales table via a date column and with a week number column , and you are slicing/filtering by the week number column, then it is a simple(assuming Matrix has Calendar[Week number] on rows):

Total Sales = SUM [ table[Sales] )

Sales YTD = TOTALYTD ( [Total Sales], Calendar[Date] )

Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR( Calendar[Date] ) )

Sales YTD last Year = CALCULATE ( [Sales YTD], SAMEPERIODLASTYEAR ( Calendar[Date] ) )