cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Line chart´s problem

Hi all,

I generated a line chart where its shown a KPI measured in three different ways:

Monthly (Cyan one)= Monthly measurement of the KPI

Accumulated (Black one) = measured from the beginning of a year to a specific month of the same year (should restart each January)

12 Month Moving Average (Red one) = Represents the running total of a 12 month period

For each ways, I used the following formulas:

Monthly:

KPI CAP Monthly= 1-SUM('DB'[cli*t*g ap])/([Total costumers]*[days])

Accumulatd:

KPI CAP Accumulated=
CALCULATE (
1-SUM('DB'[cli*t*g ap])/([Total costumers]*[days]),
FILTER (
ALLSELECTED('DB'[date]),
'DB'[date] <= MAX ( 'DB'[date] )

12-Month:

KPI CAP MM =
CALCULATE (
1-SUM('DB'[cli*t*g ap])/([Total costumers]*[days]),ALL('DB'[year),
DATESINPERIOD('DB'[date],LASTDATE('DB'[date]),"-11",MONTH))

I set 1 slicer in the report: Year (DB[year]) and contains 2 years: 2015 and 2016.

When I analyze the values for each year separately, the formulas works great. But when I see the whole line chart (2015 AND 2016) the Accumulated presents errors.

As you can see in the following pictures, the acummulated kpi in 2015 finish different at the monthly kpi (Thats correct) and restart in 2016 (same value as Janury's monthly KPI in 2016). The first chart is with 2015 slicer activated and the second one is with 2016 slicer activated. When I select Both years (to analyze the whole values) the accumulated KPI changes and does not restart with the year. In addition, selecting both years, the Accumulated KPI´s values change for the whole dates!

I'm trying to get the same values for this kpi independent of the selection of years. I would appreciate someone could help me!

201520162015 and 2016

1 ACCEPTED SOLUTION
Microsoft Employee

@efanta

In this scenario, you didn't specify filters on Year level. So when you select both years, it only takes all dates as context for calculation without respecting current year. Your formula can be like:

KPI CAP Accumulated=
CALCULATE (
1-SUM('DB'[cli*t*g ap])/([Total costumers]*[days]),
FILTER (
ALL('DB'[date]),
'DB'[Year] = MAX ( 'DB'[Year] ) &&
'DB'[date] <= MAX ( 'DB'[date] )
)

Regards,

2 REPLIES 2
Microsoft Employee

@efanta

In this scenario, you didn't specify filters on Year level. So when you select both years, it only takes all dates as context for calculation without respecting current year. Your formula can be like:

KPI CAP Accumulated=
CALCULATE (
1-SUM('DB'[cli*t*g ap])/([Total costumers]*[days]),
FILTER (
ALL('DB'[date]),
'DB'[Year] = MAX ( 'DB'[Year] ) &&
'DB'[date] <= MAX ( 'DB'[date] )
)

Regards,

Frequent Visitor

It works! Thanks!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.