Forum Discussion
Linking calendar table issues
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.
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.
"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.
You have to activate the preview feature in the options
9 Replies
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
PLease try and edit the file itself if possible so I can understand more better
Thanks
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.
"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.
