My power bi is not summing or counting all my data

[cinlod]

Hello,

I’m having this problem with my power bi.

I’ve been using power bi for three weeks now and I noticed that not all my data was counted or summed. It’s just the last weeks data that was not completely summed.
Please I need a solution and all want to understand why so data won’t sum or count.
Is it that when I refresh not all my data was added because I was to sum my data source to be sure.

Please I need all the help I can get.

Many thanks.

[dilumd]

Can you share some data and the function you use?

[cinlod]

Hello,

Thanks for your responses. Clearly, i discovered that the problem is not the measure but the calendar, (weeks). You will notice that the total is the same from the two tables but week 44 from my calender table give a different result.

Please, how to i solve this problem.

Below is a snip shot of my screen.

Thanks,

[dilumd]

Hi

It’s very difficult to tell what’s happening in your data set with this less detail.

It’s looks like your week no column in the right-side table is a text and left side table it’s numeric.

How did you relate your transaction detail table with the calendar table?

Can you add the year column to the table in the right side?

[cinlod]

Hello,

Yes you're right the week that calculates correctly is date, that is text.

I'm just comfused. Waiting to get to work tomorrow to add more data and see the result.

Will get back to you after i've added more data.

Thanks so much. 

[cinlod]

Hi,

What i'm trying to calculate is previous week's sales.

Any help on solving it.

Thanks

[v-sihou-msft]

@cinlod

I suggest you make all week number columns into numeric type. Or you can generate a calculated column with WEEKNUM() functinon. 

Then you can create a measure like: 

=
CALCULATE (
    SUM ( Table[Sales] ),
    FILTER (
        ALL ( 'DimDate' ),
        DimDate[Year] = MAX ( DimDate[Year] )
            && DimDate[WeekNumber]
                = MAX ( DimDate[WeekNumber] - 1 )
    )
)

Regards,

[cinlod]

Hi Simon,

Thanks so much for your response.

I tried the formula and got an error message; "The MAX function only accepts a column reference as an argument".

what can i do next.

Thanks,

[v-sihou-msft]

@cinlod

Sorry for my mistake, it should be like: 

=
CALCULATE (
    SUM ( Table[Sales] ),
    FILTER (
        ALL ( 'DimDate' ),
        DimDate[Year] = MAX ( DimDate[Year] )
            && DimDate[WeekNumber]
                = MAX ( DimDate[WeekNumber]  ) -1
    )
)

[dilumd]

Week no is there in your calendar table and transaction table?

This difference can occur due to many reasons. Since you have two week no columns I can think of below causes,

1. Since you're using two different week no columns results may differ, try to use one column coz that will confuse you.

2. Since week no column is a text in one scenario and numeric in other scenario summation may result in different values due to duplicates