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

[lkshck]

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?

[v-yalanwu-msft]

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.

[Greg_Deckler]

@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...

[BA_Pete · Accepted solution]

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]

@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...