DAX expression to calculate running total from specific point

[diskman]

I have got calculated table "CountryMonth" which lists country and months. For a country there can exist value represented by "Selected" measure (with some non blank value > 0). There is also a column "order" to specify order of months from April to March.

The expected is to create new measure on "CountryMonth": when Start Point is 10% and for a Country (like Afghanistan on a screenshot) the month June is selected then for all previous months (May, April) value of new Measure must be 0 %. The June must be set as 10% (because it has been Selected in slicer)) and all next months up to the end of FY (March) must be increased by 3%.

If month is not selected for a country then all country months should be set as 0%.

[v-yiruan-msft · Accepted solution]

Hi @diskman ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a date dimension table and use the month field in Date table as slicer options

2. Create a measure as below to get the CountryMonth

CountryMonth = 
VAR _selcountry =
    SELECTEDVALUE ( 'Table'[Country] )
VAR _selmonthno =
    SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
VAR _seltmonthno =
    SELECTEDVALUE ( 'Table'[Date].[MonthNo] )
VAR _para =
    SELECTEDVALUE ( 'Parameter'[Parameter] )
RETURN
    IF (
        _seltmonthno < _selmonthno,
        0,
        IF (
            _selmonthno = _seltmonthno,
            _para,
            _para + 0.03 * ( _seltmonthno - _selmonthno )
        )
    )

Best Regards

[diskman]

Hey @RY - thank you very much with proposed solution. There is one problem - I am not able to specify that for Afghanistan starting month is May and for Brazil starting Month is June. There is requirement that report user may selected different starting months for countries.

[v-yiruan-msft]

Hi @diskman ,

That's dynamic, and the users can choose to toggle the slicer options of Parameter, Country and Month to get the results they want.

Best Regards

[AlexisOlson]

If the start point is 0% or 50%, do you still increment by 3%?

[diskman]

@AlexisOlsonyou are correct, if on screenshot we would have the start point set to 0% and June would be the set for Afghanistan then the expected result should be:

March - 0%

April - 0%

May - 0%

June -0 %

July - 3%

August - 6 % etc

in case of 50%

March - 0%

April - 0%

May - 0%

June -50 %

July - 53%

August 56% etc