Error in matrix table

[prabhuk]

Hello,

can you help in identifing the error in the attached file. when i add reagion along with sales and commit table i am getting error.

https://www.dropbox.com/s/yly9bouar7rjfz5/BI%20File.pbix?dl=0

[prabhuk]

Hi,

I have adeed calender table and still i have issue in previsou year calculations.

I have created two table one is based on region and another is on date(week)

https://www.dropbox.com/s/nypm1039dlcm1n4/BI%20File.pbix?dl=0

[Ashish_Mathur · Accepted solution]

Hi,

Download the PBI file from here.

Hope this helps.

[parry2k]

@prabhuk I know @mahoneypat  gave the solution without a date table but I will never go that route Make a habit to add a date dimension in your model and perform all time intelligence based calculations based on this dimension. There is a reason why it needs to be in the model.

Just my 2 cents.

[mahoneypat]

I agree you should add a Date table to your model.  FYI that you can do your calculation without one (but you really should add one).  Here is an example.  Also, FYI that you don't need to wrap all your measures in CALCULATE, only when you need to change the evaluation context (like your previous year calculations).

FY20 2 =
VAR vThisYear =
    YEAR (
        TODAY ()
    )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] ),
        FILTER (
            ALL ( Sales[DATE] ),
            YEAR ( Sales[DATE] ) = vThisYear - 1
        )
    )

Pat

[Ashish_Mathur]

Hi,

Your tables and relationships are not properly set up.  You should create a Calendar Table and build a relationship from the Date columns of the Budget and Sales table to the Date column of the Calendar Table.  The region column of the Budget and Sales table should also be connected to the Region column of the Region table.  There should be no relationship between the Budget and Sales table.  Any time period related slicer (Year/Month Name) should be dragged from the Calendar Table.

[parry2k]

@prabhuk for any date-related calculations, you must add a date dimension in your model, and to add one, you can look at my post here.

once this table is there, change all your measures to use date from this new date table instead of from your sales table and it will all work.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡