Basic calculations. Dataset not fully shared

Power BI
1

Hi there!

My company recently engaged with PBI, however IT department isn’t sharing Data Tables nor Relationships with users that are supposed to develop some report (dataset built from SAP BW which is connected to my PBIX from PBI connection).

I’m trying to build some basic insights but struggling more than expected (can’t access Data Tables…):
Total Revenue (Monthly average rate) was created by IT. Can’t see the code.
I created these measures:

Sales Yearly Act:

    CALCULATE(
    'SPP CS reallocation'[Total Revenue (Monthly average rate)],
    DATESYTD('SPP CS reallocation'[Posting date])
    )

Which is actually acting as

Cumulative Act Sales:

    CALCULATE( [Total Revenue (Monthly average rate)],
    FILTER(ALLSELECTED('SPP CS reallocation'[Posting date]),
    'SPP CS reallocation'[Posting date] <= MAX('SPP CS reallocation'[Posting date])
    )
    )

Last, I wanted to compare vs

Sales Yearly Act PY:

CALCULATE(
[Sales Yearly Act],
ALL('Calendar'[Date]),
SAMEPERIODLASTYEAR('Calendar'[Date])
)

Problem is Sales Yearly acting like cumulative, and PY Act Sales not showing up since I have report filtering 2019. If I take out the report filter I get PY Sales but won’t work in a chart.

As to the dates, I have tried the same using [Posting date] and the outcame was the same, was just trying out with [Calendar(date)]

Thanks for helping this newbie in need. Cheers!

image
image.png802×807 66 KB

2

@SamSPAIN
Is there a way to set you Calendar table as a date table:

How%20to%20Mark%20as%20Date%20Table
How%20to%20Mark%20as%20Date%20Table1180×650 34.4 KB

Not sure if you have access to do that or not. If not the time intelligence functions begin to act weird at times. So wont be able to use them, but you can still write measures like the ones you want just in a more verbose way.

Enterprise%20DNA%20Expert%20-%20Small

3

Hi @Nick_M,

Thanks for getting back to me on this. I confirm I cannot even do this. See below screenshot with all functionalities greyed out (disabled):

image
image.png1486×420 40.5 KB

Will try check out with IT to have FULL access to the dataset (coming from SAP BW).

Quick Q, would you know if there’s any way of sharing (FULLY) a database with users without compromising the source? I mean, if it’s possible for users to make changes/massage data in Tables without impacting the very same dataset source so that if another user connects to the database he/she won’t have the calculated columns or other changes like so in the dataset.

I have done myself a bit of a research on this but still unsure whether this is doable.

Thanks a lot for your support.

4

Oh man, that is rough.

You cannot write-back to the database via PowerBI (at least not that I know of, and if you can it wouldnt be easy). Assuming you are using the import method, and not directquery, the tables are loaded into PBI. From there you use power query for all the ETL stuff and then create your datamodel. That’s a lot of what I do in my day-to-day job. IT probably doesnt know what the datamodel you want shoud look like and what you will and will not need. So not sure why it is so locked down for you, I mean all you can really do is read the data. Just an unnecessary hurdle that makes creating reports more challenging than it needs to be.

I would talk to your IT dept and see if they can unlock that for your. They should be able to give you only “Read” access. But (and take this with a grain of salt as this is just my experience) but if you are not “Traditional” IT you might run into some issues. I think a lot of IT guys view this Power BI stuff as just “excel” and not “Real” programming power. Which is crazy, but I found it to be a giant uphill battle explaining this. It was only when I created a report for my boss, which she could easily use and was 1000% faster, IT finally let me have read access. In my view Self Service BI = less traditional IT guys, which they obviously do not like. But PBI works so well because it can be developed and deployed exponentially faster than what the traditional IT guys can do.

Well, that was a tangent I didnt plan on going on, so hopefully that didnt bore you too much :slight_smile:

But long story short:

  1. See if IT can give you read access. I’d explain that you cannot write back to the database and without developing your own specific data models you are kind of stuck

  2. If that’s not going to happen, and going back to your original question, is there or can you get a calendar table?

-Nick

Enterprise%20DNA%20Expert%20-%20Small