Dates from MDS source not allowing drill down from DimDate

[Anonymous]

Morning all,

I want to show how much we have invoiced against a target.  The target changes every month and is not something we can calculate dynamically right now.  So I need my user to enter the new target each month.  So I have created a table in MDS as a datasource and used this as a table.

But I want to be able to use Time Intelligence and calculate what the target would be, and actuals are for each week number of the year and even for the day based on number of working days in the month.

Here are my current relationships:-

(Line in DimDate table is to show I have lots more columns, but couldn't fit all I needed in one screenshot)

I know that joining on month between target table and DimDate is what is causing the problem, but I don't have the target at a more granular level.

My end goal is to have a table which shows

Month Target, Month actual

and then the user can drill down to see week and daily levels.

I don't know what I need to change though?  My MDS relationship, have a completely differenst source for the target, can it all be done via DAX in measures?

Help very much appreciated!

Thank you 

[v-eqin-msft]

Hi @Anonymous ,

Sorry for my late reply.

According to my understanding, you want to show the sum in the same week of current and previous year,right?

You could use the following formula to add columns:

Week =
1 + WEEKNUM ( 'Table'[Date] )
    - WEEKNUM ( STARTOFMONTH ( 'Table'[Date] ) )

YearMonth =
YEAR ( 'Table'[Date] ) * 100
    + MONTH ( 'Table'[Date] )

Same week in last year =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Week] = EARLIER ( 'Table'[Week] )
            && 'Table'[YearMonth]
                = EARLIER ( 'Table'[YearMonth] ) - 100
    )
)

Then custom hierarchy between YearMonth and Week columns.The final output is shown below:

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Anyone any ideas?

[Anonymous]

I've never attached a sample document before, so I hope this works!

Sample power bi report 

Excel dataset 

I use SQL as a datasource in my version of the report, but to try and keep it simple and to exclude sensitive data, I have copied the data in to Excel for this version to share.

What I want to be able to do is drill through from Month to week levels and show amount invoiced cumulatively within the month and compare the week number of the month to the same week of the month last year.  For example, the third week in November vs third week in November last year.

I am only given the target at calendar month level

Any help is very much appreciated

[v-eqin-msft]

Hi @Anonymous ,

Could you please provide me with more details about your table and your problem or share me with your pbix file without sensitive data from your Onedrive for Business?

Best Regards,
Eyelyn Qin

[amitchandak]

@Anonymous , I have a couple of articles around how to del yearly target. Monthly would same, just use month functions wherever needed

refer : https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290

https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-Convert-to/ba-p/1476400

[Anonymous]

Thank you.

This article is a great starting point. I'm starting with month rather than year. What would you suggest joining on for that, as months repeat so i don't have a "one" side of a relationship in my date table.

Also my working days will be different every month so I can't apply a divide quite as easily.