This blog post explains, how to use Daily, Weekly & Monthly measures in single chart or table in Power BI. This might be familiar to you, but I keep getting this question about this.
If you want to build a chart only daily or weekly measures then it pretty easy as you can use the date dimension column and take a measure and it will show the daily result when you have proper relationship between the fact and dim – date tables.
When we want to compare daily vs weekly vs monthly, then we end up with using all the measures in single date context. We need to play with CALCULATE to overwrite the filter context for weekly and monthly measures.
First, lets see how to build individual measures. As I said, it is very simple.
I am taking below table for example.
Daily Count – Simply take date and count column and create a chart or table in Power BI.
Weekly Count – Simply take Week and count column and create a chart or table in Power BI.
Monthly Count – Simply take monthly and count column and create a chart or table in Power BI.
Now, let us try to merge them all together to achieve like below.
Let me explain to create the measures,
1. Daily Count
2. Weekly Count
3. Monthly Count.
I have created following columns – Year Week & Year Month as we need to create logic to support even for multiple years.
Year Week = Year & Week — 202014
Year Month = Year & Month — 20203
Please use the proper logic to get the correct digits. I left as simple just for demo. In a real-time, you need to handle month single and two digits. Same for week.
Daily Count – No need to build any measure, we can just directly use Date and Count column.
Weekly Count – As we are keeping date column, we need to override the context to get the sum of daily count for that specific week.
Weekly Count = CALCULATE(SUM(TD[Count]),FILTER(all(TD),TD[Year Week] = Max(TD[Year Week])))
TD – is my table name
I have used Year week column.
Monthly Count – this is same as weekly count. Use Year Month column.
Monthly Count = CALCULATE(SUM(TD[Count]),FILTER(all(TD),TD[Year Month] = Max(TD[Year Month])))
Finally, we can combine all the above measures into single visual.
Hope you like this approach. Please share if you find any other alternate options.