DAX Calendar - Single script for MAT/QTR/4WKS/Week

[RobbieRalph]

Hi there,

I have a need to be able to quickly create a calendar table that has non typical dates.

All of my data is weekly data so we typically view MAT (52 Weeks), QTR (13 Weeks), 4Wks (4 Weeks) and Week. This data will always be a total of 156 weeks (3 x 52 Weeks).

I am very new to Power BI and DAX and although I have managed to create the table that I need, I have not been able to do so in a single script (I create the calendar and then add columns). 

Any guidance on how to approach this would be appreciated.

Thanks,

Here are the formulas for the columns that I created (note that I also created an extra column for each of these to number/sort these values chronologically but i wont list them here):

MAT = if(Dates[Date]>(max(Dates[Date])-364),"MAT",if(Dates[Date]>(max(Dates[Date])-728),"MAT YA","MAT 2YA"))

QTR = if(Dates[Date]>MAX(Dates[Date])-91,"QTR to "&FORMAT(MAX(Dates[Date]),"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-182,"QTR to "&FORMAT(MAX(Dates[Date])-91,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-273,"QTR to "&FORMAT(MAX(Dates[Date])-182,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-364,"QTR to "&FORMAT(MAX(Dates[Date])-273,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-455,"QTR to "&FORMAT(MAX(Dates[Date])-364,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-546,"QTR to "&FORMAT(MAX(Dates[Date])-455,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-637,"QTR to "&FORMAT(MAX(Dates[Date])-546,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-728,"QTR to "&FORMAT(MAX(Dates[Date])-637,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-819,"QTR to "&FORMAT(MAX(Dates[Date])-728,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-910,"QTR to "&FORMAT(MAX(Dates[Date])-819,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-1001,"QTR to "&FORMAT(MAX(Dates[Date])-910,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-1092,"QTR to "&FORMAT(MAX(Dates[Date])-1001,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-1183,"QTR to "&FORMAT(MAX(Dates[Date])-1092,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-1274,"QTR to "&FORMAT(MAX(Dates[Date])-1183,"dd/mm/YYYY"),

"0"))))))))))))))

4WKS = if(Dates[Date]>MAX(Dates[Date])-28,"4Wks to "&FORMAT(MAX(Dates[Date]),"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-56,"4Wks to "&FORMAT(MAX(Dates[Date])-28,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-84,"4Wks to "&FORMAT(MAX(Dates[Date])-56,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-112,"4Wks to "&FORMAT(MAX(Dates[Date])-84,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-140,"4Wks to "&FORMAT(MAX(Dates[Date])-112,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-168,"4Wks to "&FORMAT(MAX(Dates[Date])-140,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-196,"4Wks to "&FORMAT(MAX(Dates[Date])-168,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-224,"4Wks to "&FORMAT(MAX(Dates[Date])-196,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-252,"4Wks to "&FORMAT(MAX(Dates[Date])-224,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-280,"4Wks to "&FORMAT(MAX(Dates[Date])-252,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-308,"4Wks to "&FORMAT(MAX(Dates[Date])-280,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-336,"4Wks to "&FORMAT(MAX(Dates[Date])-308,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-364,"4Wks to "&FORMAT(MAX(Dates[Date])-336,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-392,"4Wks to "&FORMAT(MAX(Dates[Date])-364,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-420,"4Wks to "&FORMAT(MAX(Dates[Date])-392,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-448,"4Wks to "&FORMAT(MAX(Dates[Date])-420,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-476,"4Wks to "&FORMAT(MAX(Dates[Date])-448,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-504,"4Wks to "&FORMAT(MAX(Dates[Date])-476,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-532,"4Wks to "&FORMAT(MAX(Dates[Date])-504,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-560,"4Wks to "&FORMAT(MAX(Dates[Date])-532,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-588,"4Wks to "&FORMAT(MAX(Dates[Date])-560,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-616,"4Wks to "&FORMAT(MAX(Dates[Date])-588,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-644,"4Wks to "&FORMAT(MAX(Dates[Date])-616,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-672,"4Wks to "&FORMAT(MAX(Dates[Date])-644,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-700,"4Wks to "&FORMAT(MAX(Dates[Date])-672,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-728,"4Wks to "&FORMAT(MAX(Dates[Date])-700,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-756,"4Wks to "&FORMAT(MAX(Dates[Date])-728,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-784,"4Wks to "&FORMAT(MAX(Dates[Date])-756,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-812,"4Wks to "&FORMAT(MAX(Dates[Date])-784,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-840,"4Wks to "&FORMAT(MAX(Dates[Date])-812,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-868,"4Wks to "&FORMAT(MAX(Dates[Date])-840,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-896,"4Wks to "&FORMAT(MAX(Dates[Date])-868,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-924,"4Wks to "&FORMAT(MAX(Dates[Date])-896,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-952,"4Wks to "&FORMAT(MAX(Dates[Date])-924,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-980,"4Wks to "&FORMAT(MAX(Dates[Date])-952,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-1008,"4Wks to "&FORMAT(MAX(Dates[Date])-980,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-1036,"4Wks to "&FORMAT(MAX(Dates[Date])-1008,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-1064,"4Wks to "&FORMAT(MAX(Dates[Date])-1036,"dd/mm/YYYY"),

if(Dates[Date]>MAX(Dates[Date])-1092,"4Wks to "&FORMAT(MAX(Dates[Date])-1064,"dd/mm/YYYY"),

"0"))))))))))))))

)))))))))))))))))))))))))

[v-lionel-msft]

Hi @RobbieRalph ,

Do you want to merge the three formulas "MAT", "QTR" and "4WKS" together? In other words, you want to return only one calculated column?
This is impossible. "MAT", "QTR" and "4WKS" are groupings of date columns. It is impossible for one column to return three different grouping results.

Best regards,
Lionel Chen

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

[amitchandak]

@RobbieRalph , First create a calendar like

I think you are looking for 4 4 5 week -https://www.sqlbi.com/blog/marco/2013/02/19/dax-time-intelligence-for-4-4-5-calendar-iso-calendar-an...

https://www.youtube.com/watch?v=JqVnqMLGWDY