How to create a measure for cumulative a line char...

saranp780 · ‎06-26-2018

I have got data like this.....

Status	Title	Name	Surname	Deparment	License Type	Adding Date
Active	Mr.	Susan	Simon	Sale	Office 365 Enterprise E3	16/9/2016
Active	Mr.	Tennassi	Koo	Sale	Office 365 Enterprise E3	16/9/2016
Active	Mr.	Andrew	Sundaen	Sale	Office 365 Enterprise E3	17/9/2016

My requirement is to calculate from their license. If it is no longer licensed, the status will change to inactive.

The Active still show on the status means these licenses are available. and continue using. In this case, there are three data from 2016 in the table. I want to get 3 in 2016, 3 in 2017 and 3 in 2018. (follow this picture).

Then if new user registration in 2019 increase 3 licenses It will be 6 licenses in 2019. (Not in this case)

Now I used this measure

Count of License Type RunningTotal By Adding Date = 
CALCULATE(
	COUNTA('Community'[License Type]),
	FILTER(
		ALLSELECTED('Community'[Adding Date]),
		ISONORAFTER('Community'[Adding Date], MAX('Community'[Adding Date]), DESC)
	)
)

And my result only shows 2016

Please give me an advice and suggest the measure that I have to do. Thank you all for your answer.

v-shex-msft · ‎06-26-2018

HI @saranp780,

Maybe you can use below measure to calculate available license amount based on calendar date:

Count of License Type RunningTotal By Adding Date =
VAR license_range = 24 //month amount
VAR calendar_date =
    SELECTEDVALUE ( calendar[Date] )
RETURN
    CALCULATE (
        COUNTA ( 'Community'[License Type] ),
        FILTER (
            ALLSELECTED ( 'Community'[Adding Date] ),
            calendar_date
                IN CALENDAR (
                    'Community'[Adding Date],
                    DATE ( YEAR ( 'Community'[Adding Date] ), MONTH ( 'Community'[Adding Date] ) + license_range, DAY ( 'Community'[Adding Date] ) )
                )
        )
    )

Notice: please use calendar date as axis and measure as value.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.