Data modelling?

Power BI Data Model
1

Hi Fellow Power BI Users -

I have created what i thought was a very simple data model and data table however I am getting strange occurrences - i can send you a data file if you need.

As you can see in this photo, i have a measure which is for revenue then a simple measure which calculates revenue LY and it works exactly as it should and is correct. However, when i remove the context of “adjusted date”, in hopes for a total of revenue rather then a breakdown, it displays 0 for revenue LY.

With date context

image
image.png950×839 131 KB

Without date context

image
image.png953×470 31 KB

data model

image
image.png1123×1274 83.1 KB

For some reason i am having weird issues where if there is no date context next to anything it will display 0.
Any help would be great thanks,

Cheers,

2

Seems very bizarre,

I would need to see more though.

What is the LY formula?

If you want to add the file I can have a look at it. I doubt it will take long to work out what could be the problem I think.

3

Hey Sam,

Did u have any luck with this query?

4

Hi Robert,

Sorry did you add more to this?

I had a few questions so usually I wait for a reply before answering again.

5

If you can add the file that would be helpful and shouldn’t take too long to work out what’s wrong.

6

Hi Sam,

I believe i emailed you a .pbix file relating to the questions as per your request.

let me know if you would like me to send you another one.

Kind Regards,

7

Hi Robert,

Sorry I can’t seem to find it anywhere. I’ve search my inbox and nothing there.

You know you can add it to this forum post?

8

Hi Sam,

here is the file hope this one works

Profit&Loss.pbix (1005.2 KB)

Kind regards,

9

The key to remember when building your model and creating visualizations is to make sure you filter by the lookup table where you can.

Especially when running time intelligence calcs like you are here

image

All I did was switch out the date column from the fact table and adding the actual date column from the data table

image

image

You see here you’re reference the date table

Revenue LY = CALCULATE([Revenue], DATEADD(Dates[Date], -1, YEAR))

So the key is to make sure to use this actually inside the table to.

You can then use any column from the date table. Like the below. I like this better if you are showing monthly data

image

See how you go with this.

Attached
Profit&Loss.pbix (1008.2 KB)