Sum up till date

user_5260265 0
Viewed 55

I am trying to summarize the data with total sums till the min date selected in the slicer with one more condition. But somehow it does not sum up. what am i missing ?

Till Date Sum =
VAR _lastdate =
    CALCULATE ( MIN ( 'Mizan Full'[Tarih] ), ALLSELECTED ( 'Mizan Full'[Tarih] ) ) - 1
RETURN
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'Mizan Full'[Firma Adı],
            'Mizan Full'[Hesap No],
            'Mizan Full'[Hesap Grup Adı],
            "Önceki Toplam", SUM ( 'Mizan Full'[Rapor USD] )
        ),
        'Mizan Full'[Hesap Grubu] = "100",
        'Mizan Full'[Tarih] <= _lastdate
    )

Edit : The problem is with the _lastdate, it calculates normally separately but when in the formula, it gets the minimum date of all data, not the selected one.

I also tried with the FILTER() as below, still not returns correct result.

Till Date Sum =
SUMMARIZECOLUMNS (
    'Mizan Full'[Firma Adı],
    'Mizan Full'[Hesap No],
    'Mizan Full'[Hesap Grup Adı],
    FILTER ( 'Mizan Full', 'Mizan Full'[Hesap Grubu] = "100" ),
    FILTER (
        'Mizan Full',
        'Mizan Full'[Tarih]
            <= CALCULATE ( MIN ( 'Mizan Full'[Tarih] ), ALLSELECTED ( 'Mizan Full'[Tarih] ) ) - 1
    ),
    "Önceki Toplam", SUM ( 'Mizan Full'[Rapor USD] )
)
dax powerbi
1 Answers

Please try this:

VERSION-1

Till Date Sum =
VAR _lastdate =
    CALCULATE(MINX ( ALLSELECTED ( 'Mizan Full'[Tarih] ), 'Mizan Full'[Tarih] ))
RETURN
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Mizan Full',
                'Mizan Full'[Firma Adı],
                'Mizan Full'[Hesap No],
                'Mizan Full'[Hesap Grup Adı]
            ),
            "Önceki Toplam", CALCULATE ( SUM ( 'Mizan Full'[Rapor USD] ) )
        ),
        'Mizan Full'[Hesap Grubu] = "100",
        'Mizan Full'[Tarih] <= _lastdate
    )

VERSION-2

Till Date Sum =
VAR _lastdate =
    CALCULATE (
        SELECTEDVALUE ( 'Mizan Full'[Tarih] ),
        MINX ( ALLSELECTED ( 'Mizan Full'[Tarih] ), 'Mizan Full'[Tarih] )
            = MIN ( 'Mizan Full'[Tarih] )
    )
RETURN
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Mizan Full',
                'Mizan Full'[Firma Adı],
                'Mizan Full'[Hesap No],
                'Mizan Full'[Hesap Grup Adı]
            ),
            "Önceki Toplam", CALCULATE ( SUM ( 'Mizan Full'[Rapor USD] ) )
        ),
        'Mizan Full'[Hesap Grubu] = "100",
        'Mizan Full'[Tarih] <= _lastdate
    )
user_194690880
Related