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

[Anonymous]

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 🙂

[v-sihou-msft · Accepted solution]

@Anonymous

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
)

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

Regards,

[Anonymous]

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
)