Forum Discussion
Matrix with different calendar filters
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:
- 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).
- A sample sales deals table (with columns for Deal ID, date, value, commision and client ID)
- A sample client table (with client ID, registration date, geolocation)
- 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.
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!AntonIt'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.