Create a monthly cumulative comparison between target and actual?

[SevsBo]

I have a goal of creating a monthly comparison between the cumulative yearly Target and cumulative yearly Sales to analyse the gap for the prior month.

If I have a table Targets with fixed monthly targets, I can quite easily transform that into Cumulative Monthly Target, before it's loaded in PBI, but what is the best way of getting a cumulative monthly total based on Sales Date?

In my mind I'm thinking of doing the following:

• Convert all Sales Dates to start of month, so we only get one date (or a Month-Year format)
• Create a calculated table of 12 months, each summing the Sales Vales from the Sales table and adding the results of the prior month to it. So Jan = SUMX..., Feb = Jan + SUMX..., March = Feb + SUMX...
• Link the Sales & Target tables with a Date Table & use the three to make a KPI visual filtered on last month.

Would that work and is there a more efficient way of doing this?

[v-yohua-msft · Accepted solution]

Hi, @SevsBo 

Based on your information, I create sample tables:

Create a new column in the Sales table to convert SalesDate to month-year format:

MonthYear = FORMAT(Sales[SalesDate], "YYYY-MM")

Create new measures:

Cumulative Sales = 
CALCULATE(
    SUM(Sales[SalesValue]),
    FILTER(
        ALL('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Cumulative Target = 
CALCULATE(
    SUM(Targets[TargetValue]),
    FILTER(
        ALL('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Create a relationship to make sure that the Sales and Targets tables are associated with the Date table through the date column.

Then create a KPI visual, and in the Power BI report view, select the Insert tab, and then select the KPI visual.

Drag the Cumulative Sales measure to the Metric field in the KPI visual. Drag the Cumulative Target measure to the Goal field in the KPI visual. Drag the date column from the Date table to the Trend axis field in the KPI visual. Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @SevsBo  - you have a Date Table (dimDate) with a continuous range of dates and mark it as a date table in Power BI.

use a DAX measure to calculate cumulative sales up to the last completed month dynamically.

Cumulative Sales =
VAR MaxVisibleDate = MAX('dimDate'[Date])
RETURN
CALCULATE(
SUM(Sales[Sales Amount]),
FILTER(
ALL('dimDate'),
'dimDate'[Date] <= MaxVisibleDate
)
)

create another cumulative target measure as below:

Cumulative Target =
VAR MaxVisibleDate = MAX('dimDate'[Date])
RETURN
CALCULATE(
SUM(Targets[Target Amount]),
FILTER(
ALL('dimDate'),
'dimDate'[Date] <= MaxVisibleDate
)
)

Now you can calclate the Gap = [Cumulative Sales] - [Cumulative Target]

To show the KPI only for the last completed month

Last Completed Month Cumulative Sales =
VAR LastMonth = EOMONTH(TODAY(), -1)
RETURN
CALCULATE(
[Cumulative Sales],
'dimDate'[Date] = LastMonth
)

Hope this helps. please check and confirm.