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

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
Community Champion

@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

9 REPLIES 9
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.

@ 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...
Helper V

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:

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

@ 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...
Community Champion

@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

Helper IV

Hi,

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

Please help.

Helper V

Hi,

Thank you for the update!

Earlier function is not working in my dashboard.

Could you please provide an alternative?

Community Champion

@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

Helper V

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.

Community Champion

@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’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.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors