Matrix with different calendar filters

[AntonV]

Hi,

I am new to Power BI and would like to start by creating a dashboard to have a broad overview of our progress in a matrix.

In the table below, you see what I would like to create:

	Current week	Last week	Current month	Objective month	% current vs objective	% current vs last year
# new clients
# clicks
# sales deals
Value sales (€)
Value profit (€)

I am struggling to create the filters on the top row as they are pulled from different tables etc.

The tabels I have are:

• Clients
• Clicks
• Sales deals (with value & profit in a column)
• Objectives (per week for each measurement)
• Calendar table

Is there someone who can help me with this?

Thanks in advance!

[lbendlin]

Before you do any of that you need to define what "current week"  means.  "Current"  in Power BI means "the filter context you are looking at at the moment".

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[AntonV]

Hey @lbendlin ,

Thanks for your respons.

I tried to make a sample data file but I left some values out to make it easier (f.e. the count of the clicks as it would be the same as the count of new clients).

You can find the file via this link. You will find some different tabs:

1. The dashboard I want to make with for every column an explenation of what the filter should display (this explenation is attached to the cell as a comment).
2. A sample sales deals table (with columns for Deal ID, date, value, commision and client ID)
3. A sample client table (with client ID, registration date, geolocation)
4. A sample objectives table (with objectives for each month for the rest of the year for every metric that we display in the dashboard)

Lastly I want to clearly explain what "current week/month" means to me in this dashboard by using an example. In the sample data, I say that in the example that we are today the thursday 4th of august 2022. So when I want to display the #new clients in the current week, it means the amount of new clients we gathered from monday 1th of august until thursday 4th of august. So when it becomes friday the 5th, current week means from 1-5 august, and it keeps accumulating until sunday and we start over the next monday.

I hope my explenation is clear and that you can help me creating the dashboard.

If this is too complicated, maybe I should simplify the dashboard by just saying the current week/month is the last 7/30 days?

Hope you can help me because I am stuck!

Thanks in advance,

Anton

[lbendlin]

Here is an initial version of how you should approach this in Power BI. It's a mix of explicit and implicit measures.

Don't try to "make it look exactly like Excel"  - that will be a lot of effort for little gain.

[AntonV]

Hey @lbendlin ,

Thanks for the help!

But I see you broke down the values per week. What I really wanted to achieve was a little different... But you think that my idea is impossible to make in Power BI?

Aside from that I was curious about your calculation of "# new clients". Could you explain this to me?

# new clients =

var w = max(Dates[Week])

var a = CALCULATETABLE(values('Sales deals'[Client ID]),Dates[Week]<=w)

var b = CALCULATETABLE(values('Sales deals'[Client ID]),Dates[Week]<w)

return COUNTROWS(except(a,b))

Thanks for the help!

Anton

[lbendlin]

It's not impossible but it blocks you from using the full power of Power BI.

The measure is following the standard pattern of "never seen before" items. For each week (or whatever period you choose) it lists all items that have been encountered in the past, including the period. The same is done for the past before the period. Then you subtract one list from the other, and the result are items that have first been seen in the chosen period.

[AntonV]

Hey @lbendlin ,

Thanks for the help!!