KPI - Calculate result from month and accumulate throughout the year

[uie07923]

Hello colleagues!

I have a KPI that measures a relation of the number of people in the company by the end of month and the number of occurrences of an activity within that certain month.

It divides the occurrences / number of people.

To bring the accumulated result for more than 1 period, it needs to make that calculation for each month individually and sum the result.

However, as I have a list of people for each month, doing a countrows sums all the number of people of all months - not bringing the correct result.

Can you please please support on the measure of advices?

[Anonymous · Accepted solution]

Hi @uie07923 ,

You could create a measure like below:

measure =
IF (
    ISINSCOPE ( 'Table'[date].[Month] ),
    [Fluctuation],
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[date].[Year] = SELECTEDVALUE ( 'Table'[date].[Year] )
        ),
        [Fluctuation]
    )
)

Best Regards,

Jay 

[amitchandak]

@uie07923 , not very clear, if you have date you can use datesytd

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

or

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))

Cumulative across year

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.