Forum Discussion

RobbieRalph's avatar
RobbieRalph
Frequent Visitor
5 years ago

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

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"))))))))))))))
)))))))))))))))))))))))))
Need Help

2 Replies