Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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
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,
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |