Forum Discussion

AntonV's avatar
AntonV
Helper I
4 years ago

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

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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's avatar
      AntonV
      Helper I
      4 years ago

      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's avatar
        lbendlin
        Super User
        4 years ago

        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.