BUG::DAX::CALCULATE WRONG ACROSS MONTHLY BOUNDARIES

[Element115]

ISSUE:

The following code:

CALCULATE(
    DIVIDE(
        CALCULATE(
            SUMX('IVDS Summary', [Count]), 
            ALLSELECTED('DimDate'[Date]),
            ALL(DimDate[Year]),
            ALL(DimDate[MonthNameShort]),
            ALL(DimDate[Day]),
            ALL(DimDate[DayOfWeekShort])
        ),
        COUNT('DimDate'[Date]),
        0
    ),
    ALLSELECTED('DimDate'[Date]),
    ALL(DimDate[Year]),
    ALL(DimDate[MonthNameShort]),
    ALL(DimDate[Day]),
    ALL(DimDate[DayOfWeekShort])
)

with a DimDate[Date] slicer filtering the 'IVDS Summary' table on its [Date] column, plus DimDate[Year], DimDate[MonthNameShort], DimDate[Day], DimDate[DayOfWeekShort] in the X-axis data wells of the Line and Clustered Column visual produces this erroneous reset in the calculation every single time a new month starts.  It's as if the CALCULATE is computed only on 30 or 31 days (depending on the month) even though the date slicers are set to 12 months:

In other words, the code above is supposed to produce a horizontal line for the whole selected period regardless of the dates on the x-axis. In this particular instance, an average over the whole period (or n days), while the columns represent the different sums for each day.  So no rocket science here. 😋

[Element115]

So just to be clear, the line is flat across a month, representing the daily average over that period of 30 or 31 days, which is what we want.  But as soon as the period goes beyond one month, say 12 months, instead of having one value for the whole 365 days, we have 12 different average values for each month even though we remove the filters on the visual coming from the visual x-axis data well (all the ALL function calls to remove year, month, day, and day of week), thus keeping only the DimDate[Date] column as a filter (coming from a slicer), which are the dates (days), that is the total number of these days is used to divide the total sum of values over that period of time, so you get an average for n number of days in the period, ie a flat line.  

But that's not what we see at every month end and start of another.

[Element115]

For example, if I did only the sum over a 3 day period, with 2 days at the end of one month, and the 3rd day being the first day of next month, this is what we get, also incorrect:

The sum is computed correctly for the first 2 days, and then once the 3rd day becomes the 1st of next month, the sum is computed only on that day.  Instead it should add the values of all 3 days and display a perfectly horizontal line.  Something weird is happening at the boundary when one month ends and another starts.

Here is the code:

        CALCULATE(
            SUMX('IVDS Summary', [Count]), 
            ALLSELECTED('DimDate'[Date]),
            ALL(DimDate[Year]),
            ALL(DimDate[MonthNameShort]),
            ALL(DimDate[Day]),
            ALL(DimDate[DayOfWeekShort])
        )

If you remove all the ALL() functions, you never get a horizontal line.

[Element115]

The code above with only ALLSELECTED(DimDate[Date]) ends up compute the sum over a period of one day instead of 3. So for let's say the fact table has column T[count], on day 1, the sum = T[count] for day 1, on day 2 the sum = T[count] for day 2, and on day 3, the sum = T[count] for day 3 INSTEAD OF:

T[count]/day1 + T[count]/day2 + T[count]/day3

Something is not working.  Of course, DAX being what it is, am I using it wrong?

[v-tianyich-msft]

Hi @Element115 ,

Thank you very much for the question! But this this seems reasonable. Since there are only twelve months in a year, when traversing the data, it is calculated as a set of 12 data points. Also there may be a possibility of misuse of ALL(), for example if you want a month's worth of data and you traverse the weekly ordinal numbers, there may be a case of spanning the same week, in which case I think traversing the year and the month is sufficient.

Best regards,
Community Support Team_ Scott Chang

[Element115]

@v-tianyich-msft but then how to you compute the sum or average over, say, 365 days if you your granularity is the date, so that the total sum is divided by 365 and not by 30 or 31 every time the month changes?

[Element115]

@v-tianyich-msft  the idea being to have a horizontal line because we divide the sum of the totals of everyday by the total number of days in the date range that is selected.  

That's what I thought the DAX code I showed you would be doing, but it does not.  

I'd appreciate it if you could help me figure out how to modify this DAX in order to achieve the horizontal average line across the selected date range, regardless of month boundaries.

[v-tianyich-msft]

Hi @Element115 ,

I can't reproduce your problem, check if your time dimension table is consistent with your main table.

Best regards,
Community Support Team_ Scott Chang

[Element115]

@v-tianyich-msft  Thanks, but I am not sure I understand what you mean by 'check if your time dimension table is consistent with your main table.'  Here is the model:

DimDate[Date] filters 'IVDS Summary'[Date] and that's about it for this particular use case where I would like to display the average over the all range of dates selected. 

The data wells of the visual are set like this, x-axis use DimDate to filter the data:

and the y-axis is this measure (apparently the inner ALL functions are not needed when you nest CALCULATEs):

DailyAvgOverPeriod = // Daily Avg for selected period
// true daily avg, ie a flat line because total sum of Count for every day / total # of days in the selected period or date range
CALCULATE(
    DIVIDE(
        CALCULATE(
            SUMX('IVDS Summary', [Count]), 
            ALLSELECTED('DimDate'[Date])
            // ALL(DimDate[Year]),
            // ALL(DimDate[MonthNameShort]),
            // ALL(DimDate[Day]),
            // ALL(DimDate[DayOfWeekShort])
        ),
        COUNT('DimDate'[Date]),
        0
    ),
    ALLSELECTED('DimDate'[Date]),
    ALL(DimDate[Year]),
    ALL(DimDate[MonthNameShort]),
    ALL(DimDate[Day]),
    ALL(DimDate[DayOfWeekShort])
)

This is strange. With the following measure, I get the same result as with the above:

CALCULATE(
    AVERAGEX(
        'IVDS Summary',
        [Count]
    ),
    ALLSELECTED('DimDate'[Date])
)

The visual looks like this:

And the only filters on the visual are:

I don't get it.  What am I missing, I wonder?  Why can't I get that darn h-line? 

[v-tianyich-msft]

Hi @Element115 ,

Seems to have found the problem, you are using a custom time field, I am using the whole Date, which contains the hierarchy.

Best regards,
Community Support Team_ Scott Chang

[Element115]

@v-tianyich-msft  Something is weird though.  I removed all the various fields from the x-axis and only used DimDate[Date] this time.  And now the visual shows time, not date on the x-axis!  And nothing in the visual.  

The DimDate is from a lakehouse via DirectQuery mode, and Date looks like this:

So in theory, it should work, but it doesn't.  The fact that time and not date is on the x-axis is not normal.