DAX – Daily vs Weekly vs Monthly Measures

ByHariharan Rajendran

DAX – Daily vs Weekly vs Monthly Measures

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.

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a MVP in Data Platform and Microsoft Certified Trainer with 10+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!