cancel
Showing results for
Did you mean:
Helper II

## Create Measure or Calculated Table from Daily values to get Monthly values

Hi,

I'm currently having a table which looks like the following:

 date number_of_requests 01.01.2020 300 02.01.2020 125 03.01.2020 279 04.01.2020 432

So everything is based on a daily basis. My goal is to get the sum of number_of_requests on a monthly basis as I want to use them for later calculation. The visualization isn't the problem as PowerBI is showing already the monthly values based on the date hierarchy.

What is the best option to get the values in a table on a monthly basis?

1 ACCEPTED SOLUTION
Super User

Hi @lkshck ,

You need to create a calendar dimension table then relate calendar[date] (1) to fact[date] (many).

In your calendar table, add a month field that can be used in visuals.

You can do this in Power Query something like this:

``Date.MonthName([date])``

Or in DAX like this:

``````monthName =
FORMAT(
calendar[date],
"MMMM"
)``````

Power BI will automatically aggregate your daily values to monthly values when implemented like this.

This can be extended for years, financial years, quarters etc.

Once you have the calendar table set up correctly (and marked as a date table), you can then use Power BI's time intelligence functions for your calculations. Things like Month to Date, Year to Date etc. are all in-built DAX functions that become available.

Pete

4 REPLIES 4
Community Support

Hi, @lkshck ;

You could create a measure as follows:

``sum = CALCULATE(SUM([number_of_requests]),FILTER(ALLSELECTED('Table'),EOMONTH([date],0)=EOMONTH(MAX([date]),0)))``

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@lkshck Maybe:

``````Table 29a =
VAR __Table =
SUMMARIZE(
'Table29',
"Month",MONTH([date]),
"Year",YEAR([date])
),
[Year],
[Month],
"number_of_requests",SUM('Table29'[number_of_requests])
)
RETURN
__Table``````

You could also reference the query for the source of the data and create month and year columns in PQ and do a Group By...

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
Super User

Hi @lkshck ,

You need to create a calendar dimension table then relate calendar[date] (1) to fact[date] (many).

In your calendar table, add a month field that can be used in visuals.

You can do this in Power Query something like this:

``Date.MonthName([date])``

Or in DAX like this:

``````monthName =
FORMAT(
calendar[date],
"MMMM"
)``````

Power BI will automatically aggregate your daily values to monthly values when implemented like this.

This can be extended for years, financial years, quarters etc.

Once you have the calendar table set up correctly (and marked as a date table), you can then use Power BI's time intelligence functions for your calculations. Things like Month to Date, Year to Date etc. are all in-built DAX functions that become available.

Pete

Super User

@lkshck , Refer my blog on the similar topic if that can help

Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.