Created a Date table but values are not accurate

[joannageorge]

Hi guys! You guys are the most helpful community ever, so here goes my question. 

I have created a Date table using the Invoice Date column in my Sales table. It is also marked as date table.

Below is my relationship view. 

So right now, Im trying to test if the Date table is working correctly. But I noticed that the value is not accurate. I've also posted the expected values from my Excel pivot below for the same customer (Customer A) and same filter 'CA'.

Expected results below. Same customer, same filter on Sales Method. 

Can anyone tell me why this is happening? Any help is greatly appreciated. Thank you soo much! 🙂

Cheers.

[HotChilli · Accepted solution]

Hello @joannageorge ,

this took a while to get my head around. I see 3 issues:

1 - the date table field ('Date') is datetime and it links to invoice date which is date type.  I advise making them both date.  This doesn't affect anything in this model but something to watch out for in future.

2 - watch out for rounding issues. The 'shipped quantity' field is rounded to 2 places but if it's used in any total, the unrounded figure gets used (which may show differences once they're summed).  I've seen this issue but never investigated why powerbi does this.  It might be better to do the rounding in Power Query.

3- This is the cause of the problem you're seeing.  Let's take invoice period 2021-02 as an example.  There are 10 records in the raw data (with the filters applied).  They add up to 1226.  6 of the records have invoice dates in Feb 21 (they add to 782), 4 have invoice dates in March 21 (they add to 443.5).  1226 - 443.5 = 782 which is the figure in the problem visual.

The date table is generated from Invoice date so the four March 21 entries show up in the March section of the matrix.  And this will be similar for other monthly totals that don't match the Excel.

So what are the choices now? : 

You can make a decision about going with invoice date i.e. accept that the current powerbi values are correct. 

OR you can change the relationship from date table -> Invoice fiscal period (I haven't checked this but it looks like this field indicates the Excel data relationship) 

OR you can create a dimension table from the text version of Invoice Fiscal Period - I see it gets changed to date early on in the Power Query transform (this is the main reason it took a while to find the issue as the powerbi data visual all looked correct).

----

Let me know what you think

[joannageorge]

Thank you so much for your reply, @HotChilli .

We are not able to change the relationship from date to fiscal period (month). Its a many to many relationship and its not a unique value. 

I followed your advice and changed fiscal period in query to text form again, and i also used a different date table, now using MDX, and that too works. 

Thank you so much for your help. Really appreciate it!

[HotChilli]

It's difficult to say what's wrong without seeing the data and the relationships more fully.

Maybe you can check that the relationship between the date table and fact table is working because I notice that all the dates in the date table have 12:00 am time as well (are they matching correctly to the fact table?). 

If that's all working correctly maybe you can focus your enquiry down to one particular month which shows a difference, hopefully with not too many entries and see what's included or not included.

[joannageorge]

The 12am time is just the datetime format. I dont think it affects the results. Some deeper digging showed me that it my table was summing up March 2020 + April 2020 data. That is weird. 

Many of the other months also show this problem.

Can i DM you my file, and if you could take a look at it, I would greatly appreciate it. 

[HotChilli]

Sure.  It won't be today but I'll try and look tomorrow