Forum Discussion

lkshck's avatar
lkshck
Helper III
4 years ago
Solved

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?

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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-Convert-to/ba-p/1657798

     

     

    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-or-end-date/ba-p/1503785

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

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

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    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.