Power BI: Simple addition give wrong result

Question

I'm counting number of unique IDs per month in given timeframe and I've ancountered two strange things: 1. Looking for the same thing but using two different approaches (value for each month and cumulative value month by month) gives different values. See screenshot below. 2. When You'll add by hand values in first column (monthly value) the result is 868, when PowerBI summerize it - it's 864 o_O

any ideas?

DAX Formulas below:

Y-1 Kandydaci = CALCULATE(
    distinctcount(getDataForTeb[ID_DANE_OSOBOWE]);
    DATESBETWEEN(
        getDataForTeb[Złożenie podania];
        DATE(YEAR(now())-1;4;1);
        IF(DATE(YEAR(NOW())-1;MONTH(NOW());DAY(NOW()))<=DATE(YEAR(NOW())-1;11;30);
            DATE(YEAR(NOW())-1;MONTH(NOW());DAY(NOW()));DATE(YEAR(NOW())-1;11;30)));
            ISBLANK(getDataForTeb[REZYGNACJA_DATA]))

Y-1 Kandydaci cumulative = CALCULATE(
    DISTINCTCOUNT(getDataForTeb[ID_DANE_OSOBOWE]);
    FILTER(
        ALL (getDataForTeb);
        AND (
            getDataForTeb[Złożenie podania] <= MAX(getDataForTeb[Złożenie podania])-364;
            AND (
                getDataForTeb[Złożenie podania] <= DATE(YEAR(NOW())-1; 11; 30);
                getDataForTeb[Złożenie podania] >= DATE(YEAR(NOW())-1; 4; 1)
            )
        )
    );
    ISBLANK(getDataForTeb[REZYGNACJA_DATA])
)

Another interesting example just from a while ago: different file, no DAX involved:

Isn't `DATE(YEAR(NOW())-1;MONTH(NOW());DAY(NOW()))<=DATE(YEAR(NOW())-1;11;30)` the same as `NOW() <= DATE(YEAR(NOW());11;30)`? I'm getting confused trying to see what your measure is really doing. — mendosi, Oct 12 '17 at 14:44
Yes, but if 1 year before now is before 30 November 1 year ago, then it is also true that now is before 30 November this year. — mendosi, Oct 12 '17 at 14:47
But i need data from given period one year back to compare with same period this year — Uzzy, Oct 16 '17 at 08:02
I mean the first part of the IF(), not the second or third arguments. — mendosi, Oct 16 '17 at 08:18
IF same day year ago was before 30th of November, then set it to same day one year ago, else set it to 30th of November one year ago. It's about lasy day of measurment for comparison. — Uzzy, Oct 16 '17 at 09:07
@Uzzy - Can you provide any sample data so that I can test a few ideas out on representative data? — Joe G, Oct 17 '17 at 14:04
It would be difficult - there are few several tables with relations and a lot of columns in each table — Uzzy, Oct 20 '17 at 09:36

score 2 · Accepted Answer · answered Oct 12 '17 at 14:13

2

Yes! This is the magic of DISTINCTCOUNT(). It has counted the number of distinct values for the [ID_DANE_OSOBOWE] column in each month, but when the measure is evaluated for all months, it does not double count the values which appear in more than one month.

Simplified:

| ID | Month |
+----+-------+
| 1  | March |
| 1  | April |

When you have a measure My Measure = DISTINCTCOUNT(tbl[ID]) for each month the value will be 1, but when you do a distinct count for all months then the value will still be 1 because there is only one distinct value.

answered Oct 12 '17 at 14:13

mendosi

2,001
1
12
18

I've checked the data in excel, filtered out the same period on [Złożenie deklaracji] and counted the IDs that appeared more then once with different dates (in different months) and there are 7 of them. Other interesting thing is that colleague in my office is working on different report with different data source and is using the CALCULATE(SUM(... to count some things and in his case also Grand Total is not correct :/ – Uzzy Oct 12 '17 at 14:26
Do some of the IDs occur more than one time in the same month? – mendosi Oct 12 '17 at 15:04
No, but it might some time in the future. At this moment no. – Uzzy Oct 12 '17 at 20:39

score 0 · Answer 2 · answered Mar 18 '19 at 12:28

In general when you're getting strange results when grand total (calculated automatically) is different then sum of partial results it is either the case explained by mendosi above (regarding DISTINCTCOUNT) because of switching filter context for each row of calculation, or because some calculations count BLANK values as 1 - in March 2019 update of PowerBI a new DAX function was introduced: DistinctCountNoBlank which eliminates counting BLANK values.

Power BI: Simple addition give wrong result

2 Answers2