cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kudo Kingpin
Kudo Kingpin

How to import master calendar to my sales order PBI file ?

Hi All

 

I have below PBI file consisit only master calendar from Mr Amit working fine :-

 

https://www.dropbox.com/s/klm9zz8q80xlhps/preWebinarPostCal_PY_DATA%20V004.pbix?dl=0

 

I cannot make use of above master calendar PBI file to import my sales order raw data file , The date unable to convert to text.

 

I seek advise how to import master calendar to my PBI file ?

 

Hope some one can share with me.

 

Paul Yeo

 

 

1 ACCEPTED SOLUTION
Super User II
Super User II

@Paulyeo11 

maybe you can try this to create a new calendar table. However, you need to do some modification since it may be now 100% suitable for your real business.

 

Dates = 
ADDCOLUMNS (
    CALENDAR (date(2020,1,1), date(2020,12,31)),
	    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
	 "Year", YEAR ( [Date] ),
	    "FinancialYear", IF (MONTH ( [Date]) < 7, YEAR ( [Date]), YEAR ( [Date]) + 1),
	    "FinancialPeriod", IF (MONTH ( [Date]) < 7, MONTH ( [Date]) + 6, MONTH ( [Date]) - 6),
	    "YYYY-MM", FORMAT ( [Date], "YYYY" ) & "-" & FORMAT ( [Date], "MM" ),
	    "Monthnum", MONTH ( [Date]),
	    "Monthnumber", FORMAT ( [Date], "MM" ),
	    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
	    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
	    "MonthNameShort", FORMAT ( [Date], "mmm" ),
	    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
	    "DayOfWeekNumber", WEEKDAY ( [Date] ),
	    "DayOfWeek", FORMAT ( [Date], "dddd" ),
	    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
	    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
	    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
	"MonthofYear", FORMAT( [Date], "mmmm-yyyy")
)

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Super User II
Super User II

@Paulyeo11 

maybe you can try this to create a new calendar table. However, you need to do some modification since it may be now 100% suitable for your real business.

 

Dates = 
ADDCOLUMNS (
    CALENDAR (date(2020,1,1), date(2020,12,31)),
	    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
	 "Year", YEAR ( [Date] ),
	    "FinancialYear", IF (MONTH ( [Date]) < 7, YEAR ( [Date]), YEAR ( [Date]) + 1),
	    "FinancialPeriod", IF (MONTH ( [Date]) < 7, MONTH ( [Date]) + 6, MONTH ( [Date]) - 6),
	    "YYYY-MM", FORMAT ( [Date], "YYYY" ) & "-" & FORMAT ( [Date], "MM" ),
	    "Monthnum", MONTH ( [Date]),
	    "Monthnumber", FORMAT ( [Date], "MM" ),
	    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
	    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
	    "MonthNameShort", FORMAT ( [Date], "mmm" ),
	    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
	    "DayOfWeekNumber", WEEKDAY ( [Date] ),
	    "DayOfWeek", FORMAT ( [Date], "dddd" ),
	    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
	    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
	    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
	"MonthofYear", FORMAT( [Date], "mmmm-yyyy")
)

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Hi Ryan

   CALENDAR (date(2020,1,1), date(2020,12,31)),

May i ask you for the above line of code , it is mean that during 2021 Jan 1 , i need to manually modify the above date ?

If your answer is Yes  , it there any way can make it dynamic ?

 

@Paulyeo11 

you can use DAX, something like below

calendar(min(table[salesdate]),max(table[salesdate]))

or use calendarauto

https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Sir

You are right , i need to create master calendar then import it since it is not so diffcuilt.

Paul

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors