PBI based Trial Balance with Date Slicer

[ahmedalam]

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

[ahmedalam · Accepted solution]

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

    )

)

[xifeng_L]

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~

[ahmedalam]

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

    )

)

[xifeng_L]

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~

[ahmedalam]

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

[xifeng_L]

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

[ahmedalam]

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

[ahmedalam]

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]

[ahmedalam]

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