Aggregate monthly data from daily results

[Peavey]

Hi,

How can I get this result from daily targets and actuals into an aggregated result to show this graph? 

Tips appreciated 🙂

[Anonymous · Accepted solution]

Hi, @Peavey 

Thanks for the reply form Sahir_Maharaj and DataNinja777. You can try their method, or try the following dax.

Target ACC = 
VAR _CurrentDate = MAX('Table'[Date]) 
VAR _StartOfMonth = EOMONTH(_CurrentDate, -1) + 1  
RETURN
CALCULATE(
    SUM('Table'[Target]),
    DATESBETWEEN('Table'[Date], _StartOfMonth, _CurrentDate)
)

Actual ACC = 
VAR _CurrentDate = MAX('Table'[Date]) 
VAR _StartOfMonth = EOMONTH(_CurrentDate, -1) + 1  
RETURN
CALCULATE(
    SUM('Table'[Actual]),
    DATESBETWEEN('Table'[Date], _StartOfMonth, _CurrentDate)
)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Peavey]

@Anonymous  could this also be used to do the same graph from Monday 07:00 to next Monday 07:00? 

[Peavey]

Thanks to all yes! @Sahir_Maharaj  & @DataNinja777 

The solution @Anonymous  had fit my model the best and it worked perfectly! Many thanks 🙂

[Sahir_Maharaj]

Hello @Peavey,

I would recommended to use a Date table.

DateTable = 
ADDCOLUMNS(
    CALENDAR(MIN('DailyData'[Date]), MAX('DailyData'[Date])),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "YearMonth", FORMAT([Date], "YYYYMM")
)

Then, create DAX measures to aggregate the daily targets and actuals

MonthlyTarget = 
CALCULATE(
    SUM('DailyData'[Target]),
    ALLEXCEPT('DateTable', 'DateTable'[Year], 'DateTable'[Month])
)

MonthlyActuals = 
CALCULATE(
    SUM('DailyData'[Actual]),
    ALLEXCEPT('DateTable', 'DateTable'[Year], 'DateTable'[Month])
)

Hope this helps.

[DataNinja777]

Hi @Peavey ,

To create a Month-to-Date (MTD) measure, first ensure you have a calendar table with a continuous date range. Then, you can use the TOTALMTD DAX function to calculate the MTD values for actual and target metrics.

Here’s how you might write the measures:

Actual MTD = 
CALCULATE(
    SUM(FactTable[ActualValue]),
    DATESMTD('Calendar'[Date])
)

Target MTD = 
CALCULATE(
    SUM(FactTable[TargetValue]),
    DATESMTD('Calendar'[Date])
)

These measures will calculate the cumulative sum of actual and target values from the beginning of the month up to the current date in the selected month, as defined by your calendar table. Be sure your calendar table is correctly related to the fact table(s) by date.

Best regards,