help needed to get the cumulative of distinct values per period

[grkanth81]

Hi all,

We are using Power BI Report Server (May 2025 version). I would like to get cumulative/running total of BilledAmount distinct values per period. 

Here is my sample test data:

Period	DateValue	EmployeeID	BilledAmount
202601	1/07/2025	123456	34900.8
202601	2/07/2025	123456	34900.8
202601	8/07/2025	123456	34900.8
202601	15/07/2025	123456	34900.8
202601	18/07/2025	123456	34900.8
202602	3/08/2025	123456	44158.94
202602	13/08/2025	123456	44158.94
202602	29/08/2025	123456	44158.94
202603	5/09/2025	123456	38486.41
202603	12/09/2025	123456	38486.41
202604	1/10/2025	123456	62589.27
202604	10/10/2025	123456	62589.27
202604	15/10/2025	123456	62589.27
202604	21/10/2025	123456	62589.27
202605		123456
202606		123456
202607		123456
202608		123456
202609		123456
202610		123456
202611		123456
202612		123456

The below DateTable holds the data from 01/06/2025 to 30/06/2026

DateTable =

ADDCOLUMNS(

    CALENDAR(MIN('Table'[DateValue]), MAX('Table'[DateValue])),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "MMM YYYY"),

    "MonthNumber", MONTH([Date]),

    "Dayint", DAY([Date]),

    "DayName",FORMAT([Date], "ddd"),  

    "Day",FORMAT([Date], "dd (ddd)")

)

And added the below columns to the above:

FY Period =

VAR CurrentMonth = MONTH(DateTable[Date])

VAR CurrentYear = YEAR(DateTable[Date])

VAR FiscalYear = IF(CurrentMonth >= 7, CurrentYear + 1, CurrentYear)

VAR FiscalMonth = IF(CurrentMonth >= 7, CurrentMonth - 6, CurrentMonth + 6)

RETURN

    FiscalYear * 100 + FiscalMonth

Fiscal Year = LEFT(DateTable[FY Period],4)

As there are distinct values in the result set, finding tricky. can someone help with this ?

Thanks,

grkanth81

[v-dineshya · Accepted solution]

Hi @grkanth81 ,

Thank you for reaching out to the Microsoft Community Forum.

As you mentioned in your question, you want to get cumulative/running total of BilledAmount distinct values per period. 

Please refer  below output snaps for both EmployeeID's and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[grkanth81]

Thank you  v-dineshya. That worked.

Thanks,

grkanth81

[grkanth81]

Thanks Aala_Ali & AnalyticPulse.

I tried both your codes and it looks like both are not working as expected. I am not sure if I am doing anything wrong from my side. I am not able to upload  the pbix file and there is a EmployeeID filter slicer. So the cumulatives should work for every employee. The final cumulative billed amounts for employees should be as below:

Period	EmployeeID	CumulativeBilledAmount
202601	123456	34900.8
202602	123456	79,059.74
202603	123456	117,546.15
202604	123456	180,135.42
Period	EmployeeID	CumulativeBilledAmount
202601	789999	79,679.50
202602	789999	159,676.72
202603	789999	180,573.72
202604	789999	180,617.22

Here is the sample data for couple of employees:

Period	DateValue	EmployeeID	BilledAmount
202601	1/07/2025	123456	34900.8
202601	2/07/2025	123456	34900.8
202601	8/07/2025	123456	34900.8
202601	15/07/2025	123456	34900.8
202601	18/07/2025	123456	34900.8
202602	3/08/2025	123456	44158.94
202602	13/08/2025	123456	44158.94
202602	29/08/2025	123456	44158.94
202603	5/09/2025	123456	38486.41
202603	12/09/2025	123456	38486.41
202604	1/10/2025	123456	62589.27
202604	10/10/2025	123456	62589.27
202604	15/10/2025	123456	62589.27
202604	21/10/2025	123456	62589.27
202605		123456
202606		123456
202607		123456
202608		123456
202609		123456
202610		123456
202611		123456
202612		123456
202601	5/07/2025	789999	79679.5
202601	12/07/2025	789999	79679.5
202601	14/07/2025	789999	79679.5
202601	22/07/2025	789999	79679.5
202601	28/07/2025	789999	79679.5
202602	7/08/2025	789999	79997.22
202602	20/08/2025	789999	79997.22
202602	31/08/2025	789999	79997.22
202603	12/09/2025	789999	20897
202603	24/09/2025	789999	20897
202604	3/10/2025	789999	43.5
202604	9/10/2025	789999	43.5
202604	10/10/2025	789999	43.5
202604	17/10/2025	789999	43.5
202605		789999
202606		789999
202607		789999
202608		789999
202609		789999
202610		789999
202611		789999
202612		789999

When I tried with your codes, this is what I can see:

Can you please advise if I am doing anything wrong ?

Thanks,

grkanth81

[Aala_Ali]

Hi @grkanth81 
Okay I got your point, I assume the EmployeeID slicer was the issue because we use ALL('Table') inside the measure was wiping out the EmployeeID filter, so it was summing all employees instead of just the one selected.

try to add keep filter and your measure will be like this  :

Distinct Billed per Period :=
VAR curPeriod = SELECTEDVALUE ( DateTable[FY Period] )
RETURN
SUMX (
VALUES ( 'Table'[EmployeeID] ),
CALCULATE (
MAX ( 'Table'[BilledAmount] ), 
KEEPFILTERS ( 'Table'[FY Period] = curPeriod )
)
)

I hope this help you to solve your problem if so, Please mark it as Solution and give Kudo.

[grkanth81]

Thanks Aala_Ali for your reply/ I tried your new code and I can see that now Distinct Billed per Period it is picking up the max values per period and by using the EmployeeID slicer. But there seems to be no change in the Cumulative Distinct Billed. They are still showing the same original max values per period but doing cumulatives.

Thanks,

Ravi

[Aala_Ali]

Hi @grkanth81 ,

If I got your point correctly

Bring FY Period down to the fact table

-- In 'Table' (fact)
FY Period = RELATED ( DateTable[FY Period] )

Add these two measures

Distinct BilledAmount per period (deduping daily repeats per employee)

Distinct Billed per Period =
VAR curPeriod = SELECTEDVALUE ( DateTable[FY Period] )
RETURN
SUMX (
-- One row per EmployeeID for the selected period
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[FY Period] = curPeriod ),
'Table'[EmployeeID],
"Amt", MAX ( 'Table'[BilledAmount] )
),
[Amt]
)

Cumulative (running) total across fiscal periods

Cumulative Distinct Billed =
VAR curPeriod = SELECTEDVALUE ( DateTable[FY Period] )
RETURN
CALCULATE (
[Distinct Billed per Period],
FILTER (
ALLSELECTED ( DateTable[FY Period] ),
DateTable[FY Period] <= curPeriod
)
)

Before all of this make sure:

Your Date table is marked as a date table, the relationship is active 1→many and Cross-filter direction is Single.

If this helps please mark my answer as Solution and drop a Kudos 🙏

[AnalyticPulse]

hi @grkanth81 
i am able to get cumulative sum with below formula:

Cumulative Distinct Billed =

VAR CurrentPeriod = MAX(tt[Period])

VAR UniqueBefore =

    CALCULATETABLE(

        DISTINCT(tt[BilledAmount]),

        FILTER(

            ALL(tt),

            tt[Period] <= CurrentPeriod

        )

    )

RETURN

SUMX(UniqueBefore, [BilledAmount])

adjust the attribute names and check if this serves your purpose.

My Work:
Analytic Pulse Blog 
Docynx Productivity Tools