cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lkshck
Helper II
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:

datenumber_of_requests
01.01.2020300
02.01.2020125
03.01.2020279
04.01.2020432

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
BA_Pete
Super User
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

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1632710778734.png

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.

Greg_Deckler
Super User
Super User

@lkshck Maybe: 

 

Table 29a = 
    VAR __Table =
        SUMMARIZE(
            ADDCOLUMNS(
                '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
BA_Pete
Super User
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

amitchandak
Super User
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 !!

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!