Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sajal161292
Helper V
Helper V

To calculate the current month of quarter in dax

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.

1 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 !!





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

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. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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:

 

SOE_DATA.PNG

 

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

soe_calendar_error.PNG

 

 

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Hi,

 

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

 

Please help.

Hi,

 

Thank you for the update!

Earlier function is not working in my dashboard.

 

Could you please provide an alternative?

@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 !!





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




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.s&op_req.PNG

 

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

@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 !!





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.