Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Proud to be a Datanaut!
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.
@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...
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
Proud to be a Datanaut!
@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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |