Linking calendar table issues

[blytonpereira]

Hi Everyone,

I have a dataset for example

Period      Brand    Value

2018M01  XX         2

2018M01  XY         3

2018M01  XZ         2

2018M02  XZ        4

I now linked the "Period" column in my dataset to my calendar table "YearMonth" column that has values similar such as 2018M01.

Since to build this relationship, only a Many (mydataset) to One (Calendar) relationship would work. Hence I removed all duplicates in my "YearMonth" column in my calendar table for the relatioship to work properly.

Now I would like to build a DAX measure for

Sales = SUM(Value)

Sales PriorYEar = Calculate (SALES, SAMEPERIODLASTYEAR(DATE)

The sales prior year equation will not work because as mentioned earlier I rmeoved duplicates in my calendar table and sales prioir year needs a full DATE range for it to work .

Any suggestions would be very healpful.

Thank you.

[mwegener · Accepted solution]

Hi @blytonpereira

you use a financial calendar.

The Power BI time intelligence works with the "normal" calendar.

So the function SAMEPERIODLASTYEAR will not compare the same financial period.

Wikipedia

"A disadvantage of the 4–4–5 calendar is that it has only 364 days (7 days x 52 weeks), meaning a 53rd week will need to be added every five or six years: this can make year-on-year comparison difficult."

Here my sample pbix file.

PBIX

[mwegener]

Hi @blytonpereira

write your data to the first of each month and don't change the calendar table.

[blytonpereira]

@mwegenerThanks for your reply.

I am not quite understanding your method as I am fairly new to POwerBI

I have shared the .pbix calendar file here

Good DRIVE

PLease try and edit the file itself if possible so I can understand more better

Thanks

[mwegener · Accepted solution]

Hi @blytonpereira

you use a financial calendar.

The Power BI time intelligence works with the "normal" calendar.

So the function SAMEPERIODLASTYEAR will not compare the same financial period.

Wikipedia

"A disadvantage of the 4–4–5 calendar is that it has only 364 days (7 days x 52 weeks), meaning a 53rd week will need to be added every five or six years: this can make year-on-year comparison difficult."

Here my sample pbix file.

PBIX

[blytonpereira]

@mwegener

Thanks a lot. This worked perfectly. I followed your steps, creating a full financial calendar...and then creating a separate unique dates calendar for firest of each month....and then appending the unique dates to my original dataset.

Then I create a relationship Many (my dataset) to One (my full financial calendar). Works great.

I have one more problem:

For exmaple the date field in your full calendar table in PowerBI, is not a hierarchy, can we create a hierarchy from this ?

Any why is it not happening automatically ?

Thank you

Blyton

[mwegener]

I'm not sure what you mean, but with drag an drop on the field list you could create your own hierarchy.

[blytonpereira]

@mwegener 

In the screenshot you uploaded it is wokring fine. However I tried downloading your file, the date Hierarchy is not create by default.

My goal is to create a simple hierarchy from the date = year quarter month day, just like in your screenshot.

 And when I try to create this hierarchy as you described desribed above it does not seem to work

 Screenshot

Screenshot 2

[mwegener · Accepted solution]

You have to activate the preview feature in the options

[blytonpereira]

@mwegenerThankyou worked perfectly

[mwegener]

You have to activate the Preview feature in the Options