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

[Paulyeo11]

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

[ryan_mayu · Accepted solution]

@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")
)

[Paulyeo11]

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 ?

[ryan_mayu]

@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

[Paulyeo11]

Hi Sir

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

Paul