Slicer MAX date minus 1 month

[Anonymous]

I'm trying to get a beginning balance. The Ending balance was simple but I dont think my logic is working for MAX minus 1 month..

Ending Balance = 
VAR
    vFirstDate = DATEVALUE("01/01/2000")
VAR
    vLastDate = MAX('TransactionTable'[DateColumn])
RETURN
    CALCULATE(
            SUM(TransactionTable[AmountColumn]),
                'TransactionTable'[DateColumn]>=vFirstDate
                && 'TransactionTable'[DateColumn]<=vLastDate)

This does not work.

Ending Balance = 
VAR
    vFirstDate = DATEVALUE("01/01/2000")
VAR
    vLastDate = MAX('TransactionTable'[DateColumn])
RETURN
    CALCULATE(
            SUM(TransactionTable[AmountColumn]),
                'TransactionTable'[DateColumn]>=vFirstDate
                && DATEADD('TransactionTable'[DateColumn]<=vLastDate,-1,MONTH))

How do I get a beginning balance?

Thank you,

[AlB]

Hi @Anonymous

What exactly would be a "beginning balance"? 

Which one of the two versions you show is not working and what's the problem?

Showing the tables in your model would certainly be helpful.

[Anonymous]

My appologies. That's what I get for copy and paste. <smile>

The second code is not working and should read Opening Balance = ...

In the last line I added the "DATEADD" portion to try and reduce the vLastDate value by one month. The first code gives me the sum of the amount column from 1/1/2000 to the last date in the slicer. I need the amount from 1/1/200 to the last day of the previous month the slicer shows.

[v-cherch-msft]

Hi @Anonymous

You may refer to below measure:

Ending Balance2 =
VAR vFirstDate =
    DATEVALUE ( "01/01/2000" )
VAR vLastDate =
    MAX ( 'TransactionTable'[DateColumn] )
VAR Pre_Month =
    DATE ( YEAR ( vLastDate ), MONTH ( vLastDate ) - 1, DAY ( vLastDate ) )
RETURN
    CALCULATE (
        SUM ( TransactionTable[AmountColumn] ),
        'TransactionTable'[DateColumn] >= vFirstDate
            && 'TransactionTable'[DateColumn] <= Pre_Month
    )

Regards,

Cherie