Running Total

[Anonymous]

Hi community,

I would be very thankfull if you assist me to solve the issue . I have issue which could not solve for days . I am quite new in DAX . In SSAS tabular cube i already wrote DAX formula which is working fine . The issue is only when you open measure in Pivot Table or in Power BI , the grand total which appears automatically ,do not sum up values in column ,but only takes the last date's value . Overall formula calculates cumulative sum of volume of past days ,then divide it to count of working days past for each day.

Here is the formula : 

СредПрджРабочихДней:=

VAR CurrentDate = MAX('Календарь'[DayDate])

VAR SelectedMonth = MAX('Календарь'[MonthNo])

VAR CumulativeSalesValue =

    CALCULATE(

        [ПервПродRKA_и_ВторПрод_Дал],

        FILTER(

            ALLSELECTED('Календарь'),

            'Календарь'[DayDate] <= CurrentDate

                && 'Календарь'[MonthNo] = SelectedMonth

        ),

        ALLSELECTED('Календарь'[DayDate])

    )

VAR WorkingDaysPast =

    CALCULATE(

        COUNTROWS('Календарь'),

        FILTER(

            ALLSELECTED('Календарь'),

            'Календарь'[DayDate] <= CurrentDate

                && 'Календарь'[MonthNo] = SelectedMonth

                && 'Календарь'[DayIsHolyday] = 0

        )

    )

RETURN

    DIVIDE(CumulativeSalesValue, WorkingDaysPast)

[Anonymous]

Hi,

First of all , thank for your quick response . Unfortunately, the code above, calculates numbers not in the way i need . For example , today is 9th March of 2023 . Totally, 6 working days has past(excluding 8th - public holiday) . So for 9th March value would be ,cumulative sum of volume sales for all days past including non-working,  then divide it to the number of business days ( working days ) past. Then for the next day the same calculation . 

By the way ,your code also in Total section , takes the last day's value

Blue marked, your'c code . To his left ,mine . 

Thanks in advance.

[Anonymous]

HI @Anonymous,

It seems like the allselected function also affect the variables with max function, you can try to use the following formula if helps:

СредПрджРабочихДней =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            'Календарь',
            [MonthNo],
            [DayDate],
            "CumulativeSales",
                CALCULATE (
                    [ПервПродRKA_и_ВторПрод_Дал],
                    FILTER (
                        ALLSELECTED ( 'Календарь' ),
                        'Календарь'[DayDate] <= [DayDate]
                            && 'Календарь'[MonthNo] = [MonthNo]
                    )
                ),
            "WorkingDaysPast",
                CALCULATE (
                    COUNTROWS ( 'Календарь' ),
                    FILTER (
                        ALLSELECTED ( 'Календарь' ),
                        'Календарь'[DayDate] <= [DayDate]
                            && 'Календарь'[MonthNo] = [MonthNo]
                            && 'Календарь'[DayIsHolyday] = 0
                    )
                )
        ),
        "CS/WDP", DIVIDE ( [CumulativeSales], [WorkingDaysPast] )
    )
RETURN
    SUMX ( summary, [CS/WDP] )

In addition, can you please share some dummy data that keep the raw data structure and dax expression? They should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Anonymous]

Hi,

What kind of data should i provide to clarify situation? I don't know what exaclty you meant by raw data structure and dax expresison. Thanks in advance

[Anonymous]

HI @Anonymous,

You can try to add a variable with summarize function to calculate these calculations on the row level and use iterator function to aggregate these results:

СредПрджРабочихДней =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( 'Календарь' ),
            [MonthNo],
            [DayDate],
            "CumulativeSales",
                VAR CurrentDate =
                    MAX ( 'Календарь'[DayDate] )
                VAR SelectedMonth =
                    MAX ( 'Календарь'[MonthNo] )
                RETURN
                    CALCULATE (
                        [ПервПродRKA_и_ВторПрод_Дал],
                        FILTER (
                            ALLSELECTED ( 'Календарь' ),
                            'Календарь'[DayDate] <= CurrentDate
                                && 'Календарь'[MonthNo] = SelectedMonth
                        )
                    ),
            "WorkingDaysPast",
                CALCULATE (
                    COUNTROWS ( 'Календарь' ),
                    FILTER (
                        ALLSELECTED ( 'Календарь' ),
                        'Календарь'[DayDate] <= CurrentDate
                            && 'Календарь'[MonthNo] = SelectedMonth
                            && 'Календарь'[DayIsHolyday] = 0
                    )
                )
        ),
        "CS/WDP", DIVIDE ( [CumulativeSales], [WorkingDaysPast] )
    )
RETURN
    SUMX ( summary, [CS/WDP] )

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng