topic PBI based Trial Balance with Date Slicer in DAX Commands and Tips

PBI based Trial Balance with Date Slicer

ahmedalam — Tue, 04 Nov 2025 07:41:15 GMT

Hello Experts,

I am developing a Trial Balance report with following criteria.

- I have created one DateTable and mark as Date Table.

- I have created one DAX based table for the list of transaction

- Created a link in between DateTable and Transaction Table

The problem is, when I have created the Slicer from DateTable w.r.t. Date the Transaction Table won't filter according to the slicer date. In slicer I am using Between.

Transaction table consist of following fields:

- Account ID & name

- Transaction amount - Dr

- Transaction amount - Cr

- Transaction date

For example:

OBCr = CALCULATE(

SUM(GJEntriesTable[Credit]),

KEEPFILTERS(DateTable[Date]))

OBDr = CALCULATE(

SUM(GJEntriesTable[Debit]))

OB = OBCr + OBDr

Re: PBI based Trial Balance with Date Slicer

ahmedalam — Tue, 04 Nov 2025 07:47:04 GMT

The value of OBDr or OB or OBCr these are all Measures

Re: PBI based Trial Balance with Date Slicer

ahmedalam — Tue, 04 Nov 2025 08:19:09 GMT

OBCr = CALCULATE(

SUM(GJEntriesTable[Credit]),

FILTER (

ALL ( DateTable ),

DateTable[Date] <= MIN( DateTable[Date] )

))

OBCr = CALCULATE(

SUM(GJEntriesTable[Credit]),

FILTER (

ALL ( DateTable ),

DateTable[Date] <= MIN( DateTable[Date] )

))

OB = [OBCr] + [OBDr]

Re: PBI based Trial Balance with Date Slicer

xifeng_L — Tue, 04 Nov 2025 09:46:26 GMT

Hi ahmedalam

From the measure expression, the result should change with the slicer.

You can check if the filtering direction on both ends of the relationship is correct, and change it to a display format with time to check if the date fields on both ends match exactly.

If not possible, you can further describe the phenomenon, such as whether the result does not change with the slicer, whether it displays null values or total values, or other situations. Additionally, sample data or PBIX files can also be provided.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: PBI based Trial Balance with Date Slicer

ahmedalam — Tue, 04 Nov 2025 10:19:57 GMT

Following are the snapshot related to the questions.

OBDr =

VAR StartDate = MIN ( DateTable[Date] )

RETURN

CALCULATE (

SUM ( GJEntriesTable[Debit] ),

FILTER (

ALL ( DateTable ), // remove filters from transactions

DateTable[Date] < StartDate

)

OBCr =

VAR EndDate = MAX ( DateTable[Date] )

RETURN

CALCULATE (

SUM ( GJEntriesTable[Credit] ),

FILTER (

ALL ( DateTable ), // remove filters from transactions

DateTable[Date] < EndDate

)

Re: PBI based Trial Balance with Date Slicer

xifeng_L — Tue, 04 Nov 2025 10:36:57 GMT

Your current expression calculates the cumulative value of OBDR and OBCR that is less than the StartDate or EndDate selected by the slicer.

If the logic is correct, then the expression is not incorrect. You can change the date range a little wider and see if the values will change.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: PBI based Trial Balance with Date Slicer

ahmedalam — Tue, 04 Nov 2025 10:41:23 GMT

I have increased or decreased the date range but no effect.

Re: PBI based Trial Balance with Date Slicer

xifeng_L — Tue, 04 Nov 2025 10:45:51 GMT

maybe is the data issue, can you upload the pbix file? if yes, pls pay attention to desensitization.

Re: PBI based Trial Balance with Date Slicer

ahmedalam — Tue, 04 Nov 2025 11:54:50 GMT

Well, it's quite difficult to publish the PBIX you know the data sensitivity. Is there any other way?

Re: PBI based Trial Balance with Date Slicer

ahmedalam — Tue, 04 Nov 2025 13:16:51 GMT

Hello Expert,

Thanks for your valueable time. I have found the solution and it's working perfectly fine.

OBDr =

VAR StartDate =

CALCULATE(

MIN ( DateTable[Date] ),

ALLSELECTED ( DateTable )

)

RETURN

CALCULATE(

SUM ( GJEntriesTable[Debit] ),

FILTER(

ALLEXCEPT( GJEntriesTable, GJEntriesTable[Account Name] ),

GJEntriesTable[AccountingDate] < StartDate

)