I was working on DAX project during last few days and found out something strange. Though DAX function set has some of very advanced date/time based functions like DATESMTD,DATESQTD,etc it lacks a few basic functions like QUARTER(). As this is something we require quite a lot while creating the date hierarchy I thought I would share the calculation I created for getting quarter and half year values. Its based on month value of date as returned by MONTH() function and expressions are
Quarter:= QUOTIENT((MONTH([ShipDate])-1),3)+1
HalfYear:=QUOTIENT((MONTH([ShipDate])-1),6)+1
You can use above expressions to create calculated columns in your Powerpivot sheet to get Quarter and HY values for including in your date hierarchy. If you want you can extend them to add letters Q and HY for display purpose
Quarter:= QUOTIENT((MONTH([ShipDate])-1),3)+1
HalfYear:=QUOTIENT((MONTH([ShipDate])-1),6)+1
You can use above expressions to create calculated columns in your Powerpivot sheet to get Quarter and HY values for including in your date hierarchy. If you want you can extend them to add letters Q and HY for display purpose
No comments:
Post a Comment