Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
AntonV
Helper I
Helper I

Matrix with different calendar filters

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 weekLast weekCurrent monthObjective 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!

6 REPLIES 6
lbendlin
Super User
Super User

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

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

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.

 

lbendlin_0-1659564117040.png

 

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

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.

Hey @lbendlin ,

 

Thanks for the help!!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors