cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mrothfusz Frequent Visitor
Frequent Visitor

Current month and three present months in a matrix with a nice format

I'm lost :-(

I want only the current month and three present months in a matrix.

When I select the YEAR and MONTH I see all the months and the format of the matrix looks very nice. When I use some calculated columns to find out the current month and the three present months with the matirx, the matrix looks horrible :-(

I use direct query! (some options are not available and yes I use "Allow unrestricted measures in DirectQuery mode" 


My calculated colums (two of four but they look the same only with a other number). Because of my regional settings I use ; instead of , in the DAX formulas

Current month:

1 (Current month) = IF ( 
	YEAR(ManHoursEntry[Start])=YEAR(NOW()) &&
	MONTH(ManHoursEntry[Start])=Month(NOW());
	MONTH(NOW()) & "-" & YEAR(NOW());"") 


Third present month:

4 (Fourth month) = IF (
	YEAR(ManHoursEntry[Start])=YEAR(NOW()) &&
	MONTH(ManHoursEntry[Start])=Month(NOW())+3;
	MONTH(NOW())+3 & "-" & YEAR(NOW());"")



The picture says a lot :-)

Current_month_with_matrix.png


1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Current month and three present months in a matrix with a nice format

@mrothfusz

 

In this scenario, if you create to get the corresponding months data, it will be 4 column groups when dragging into matrix. I assume your underlying data is on day level which will aggregate on each month. So you can should add a column to tag if the current row data is within in that 4 months.

 

Is Within Next 3 Months =
IF (
    'Calendar'[Date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
        && 'Calendar'[Date]
            <= EOMONTH ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), 3 ),
    1,
    0
)

 

 

1.PNG

 

2.PNG

 

Then you just put Months in matrix and drag above column into visual level filter to filter the 4 months data.

 

4.PNG

 

Regards,

2 REPLIES 2
Moderator v-sihou-msft
Moderator

Re: Current month and three present months in a matrix with a nice format

@mrothfusz

 

In this scenario, if you create to get the corresponding months data, it will be 4 column groups when dragging into matrix. I assume your underlying data is on day level which will aggregate on each month. So you can should add a column to tag if the current row data is within in that 4 months.

 

Is Within Next 3 Months =
IF (
    'Calendar'[Date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
        && 'Calendar'[Date]
            <= EOMONTH ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), 3 ),
    1,
    0
)

 

 

1.PNG

 

2.PNG

 

Then you just put Months in matrix and drag above column into visual level filter to filter the 4 months data.

 

4.PNG

 

Regards,

mrothfusz Frequent Visitor
Frequent Visitor

Re: Current month and three present months in a matrix with a nice format

Hi @v-sihou-msft,

 

It works perfect (I've attached a picture where the visual filter is also visible)

Thnx for helping!

My formula (be aware of the ; instead of the , )

Simon_Hou_formula = 
IF (
    'ManHoursEntry'[start] >= DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ); 1 )
        && 'ManHoursEntry'[start]
            <= EOMONTH ( DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ); 1 ); 3 );
    1;
    0
)

snip_20170522090706.png