Cumulative Total (grouped)

[EmilLykke]

Hi all!

I have a bit of a issue, and I now searched the web empty for solutions to my problem.

I have a table of customers, which I update everyday for changes in their information. If a customer gets in a new group i will create a new row in my table, with the "new" information about the customer. The table updates on every change, but i'm specially looking for changes in what group they are placed in (From 1 to 6). 
As for the picture below, you can see all of my customers on the 14. of febuary and which group they are placed in. On the 15. one customer has been moved to group 4 (the total is now 61, and not 60 as on the 14.).

I would like to show cumulative values on every date. By that showing 61 in group 4 on the 15. of february and so on for every group. 

The measure used in the picture is:

Cumulative =

VAR __TABLE =

    FILTER (

        ALLSELECTED ( Dato[Dato] ),

        Dato[Dato] <= MAX (Dato[Dato] )

    )

VAR __VAL =

    SUMX (

        __TABLE,

        [No. of Customers] -- (Just a distinctcount on customers)

    )

RETURN __VAL

[v-kaiyue-msft]

Hi @EmilLykke ,

Please follow these steps:

1.Create the measure and write the following dax expression:

Cumulative Grouped Total =

VAR SelectedDate = MAX ( 'Table'[Dato] )

VAR CumulativeData =

    FILTER (

        ALLSELECTED ( 'Table' ),

        'Table'[Dato] <= SelectedDate

    )

RETURN

CALCULATE(COUNT('Table'[No. of Customers]),CumulativeData,'Table'[Group] = MAX('Table'[Group]))

2.Create measure values so that the correct TOTAL totals can be displayed in the matrix.

Measure =

var _table=

SUMMARIZE('Table','Table'[Group],"Value_Column",[Cumulative Grouped Total])

return

IF(

    ISINSCOPE('Table'[Group]),[Cumulative Grouped Total],SUMX(_table,[Value_Column]))

3.The final result is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[EmilLykke]

Thanks @v-kaiyue-msft!
This actually gives med the same result as @amitchandak's answer. The problem is still to fill in the empty spaces, with a cumulative total for the group.

Note: I didn't get your second measure to function, but did this instead:

IF(HASONEFILTER('Table'[Group])

, [Cumulative Grouped Total]

, SUMX(VALUES('Table'[Group'),([Cumulative Grouped Total]))

)

[amitchandak]

@EmilLykke , You should prefer a separate date table in such with measures like

Cumm Sales = CALCULATE([No. of Customers] ,filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE([No. of Customers] ,filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([No. of Customers] , Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([No. of Customers] , Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

[EmilLykke]

Thanks, @amitchandak! 
I found a simalar solution the other day, but it only triggers a column, if there has been made any changes in that column. See below. This is close to what i'm looking for, but I want it to do, what it does in column 5 on the 20. So I still need a running total, even if there hasn't been made any changes. 

- This is with the measure, you replied with: 
Cumm Based on Date = CALCULATE([No. of Customers] , Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))