To calculate the current month of quarter in dax

[sajal161292]

Hi,

I have a requirement to calculate the current month of quarter in dax and then use it for further computations:

If month = 1st month of the quarter then use the value X throughout the quarter 

If month = 2nd month of the quarter then use the value Y throughout the quarter 

If month = 3rd month of the quarter then use the value Z throughout the quarter 

That is there is a value X which I need to split in the quarter for the 1st month 

there is a value Y which I need to split in the quarter for the 2nd month

there is a value Z which I need to split in the quarter for the 3rd month

This has to be done for every quarter of the year.(Q1,Q2,Q3 and Q4)

Please provide a soltion to fix the problem.

[Greg_Deckler]

Can you provide some additional details and sample data. For example, are you working with a standard calendar where January is the 1st month of Q1?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

If you are using a standard calendar, and have month numbers, you could use something like this:

Column = 
VAR __num = MOD([MonthNum],3)
RETURN IF(__num=0,3,__num)

Provides a repeating 1-3 for a standard 12 month calendar. 

[sajal161292]

Hi,

I have created Calendar table using the DAX below:

Calendar = CALENDAR(min('X'[Sdate]),max('X'[Sdate]))

and it starts from January till December

Please find the sampele data here:

Using the column that you gave above is giving this error:

[Greg_Deckler]

Column = 
VAR __num = MOD(MONTH('Calendar'[Month]]),3)
RETURN IF(__num=0,3,__num)

[PattemManohar]

@sajal161292 Please try this... but you should have Quarter and Month in your Date Dimension which can be added as below using "New Column"  

Quarter = CONCATENATE ( "Q", ROUNDUP ( MONTH ( [Date] ) / 3, 0 ))
Month = MONTH ( [Date] )

Then here is the logic you are looking for...

Test = 
VAR varRnk = RANKX(FILTER(_DimDate,_DimDate[Quarter] = EARLIER(_DimDate[Quarter])),_DimDate[Month],,ASC,Dense)
RETURN SWITCH(TRUE(),varRnk=1,"X",varRnk=2,"Y",varRnk=3,"Z")

[Swamy3105]

Hi,

If I want to get the maximum month number of the particular quarter what would be alternate DAX.

Please help.

[sajal161292]

Hi,

Thank you for the update!

Earlier function is not working in my dashboard.

Could you please provide an alternative?

[PattemManohar]

@sajal161292 Oh !! It's strange and not sure why EARLIER function is not working for you.

But anyway, here is the alternative without using EARLIER.

Test = 
VAR varCurntQuarter = _DimDate[Quarter]
VAR varRnk = RANKX(FILTER(_DimDate,_DimDate[Quarter] = varCurntQuarter),_DimDate[Month],,ASC,Dense)
RETURN SWITCH(TRUE(),varRnk=1,"X",varRnk=2,"Y",varRnk=3,"Z")

Hope this helps !!

[sajal161292]

Hi,

Thank you for helping!

Actually the requirement is that we have a value X which has 3 parts for October,November and December

X1 is for October

X2 is for November

X3 is for December

For the second month of the quarter(which is November for Q4) there is value Y which again hai 3 parts:

Y1 is for October

Y2 is for November

Y3 is for December

So now we want to have Y1 for October,Y2 for November and Y3 for December.

For the second month of the quarter(which is November for Q4) there is value Z which again hai 3 parts:

Z1 is for October

Z2 is for November

Z3 is for December

So now we want to have Z1 for October,Z2 for November and Z3 for December.

That is it should split the value according to the current month of the quarter.

I have attached a visual representation of the same above,Please let me know if you need any additional information.

[PattemManohar · Accepted solution]

@sajal161292 Ok !! Then please add an supporting column (will be helpful to understand in detail) as mentioned earlier

Rnk = 
VAR varCurntQuarter = _DimDate[Quarter]
VAR varRnk = RANKX(FILTER(_DimDate,_DimDate[Quarter] = varCurntQuarter),_DimDate[Month],,ASC,Dense)
RETURN varRnk

Then create a "New Table" as below

_NewDimDate = ADDCOLUMNS(_DimDate,"X",SWITCH(TRUE(),_DimDate[Rnk]=1,"X1",_DimDate[Rnk]=2,"X2",_DimDate[Rnk]=3,"X3")
                          ,"Y",SWITCH(TRUE(),_DimDate[Rnk]=1,"Y1",_DimDate[Rnk]=2,"Y2",_DimDate[Rnk]=3,"Y3")
                          ,"Z",SWITCH(TRUE(),_DimDate[Rnk]=1,"Z1",_DimDate[Rnk]=2,"Z2",_DimDate[Rnk]=3,"Z3")
                  )

Hope this helps !!