Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am having calender date table.
I need to show employee data received monthly from HR in matrix table in report view with date distribution.
My requirement is to show data for last 3 months for which I have used advance filter and it is working fine ion the visual.
But along with above filter I need a permanent month for last Year March month (Bold in below example table) in distributin header till next year Apr data doesnot comes.
Pls help.
Example:
Regular Employee - my requirement
Mar-22 | Jan-23 | Feb-23 | Mar-23 |
2670 | 2659 | 2657 | 2647 |
Similarly in future
Mar-23 | Apr-23 | May-23 | Jun-23 |
2670 | 2659 | 2657 | 2647 |
Further like this in future
Mar-23 | Jul-23 | Aug-23 | Sep-23 |
2670 | 2659 | 2657 | 2647 |
Hi, please find below pbix file
https://drive.google.com/file/d/1rRfzud5GX1xagIJjyPjEmj2NfB5cdMBk/view?usp=share_link
Hi @ankurbajaj07 ,
If you apply the original filter conditions, the visual will only display the data between 01-01-2023 and 31-03-2023. You can follow the steps below to get it:
1. Delete the filter with the red circle in below screemshot from Filters pane
2. Create a measure as below to judge which date should display on the visual
Flag =
VAR _seldate =
SELECTEDVALUE ( 'Main Data table'[Date] )
VAR _sdate =
EOMONTH ( TODAY (), -4 ) + 1
VAR _edate =
EOMONTH ( TODAY (), -1 )
VAR _preyear =
YEAR ( _edate )
VAR _pcondition =
IF (
MONTH ( _edate ) < 4,
_sdate >= DATE ( _preyear, 3, 1 )
&& _sdate <= DATE ( _preyear, 3, 31 ),
MONTH ( _sdate ) = 3
)
RETURN
IF ( ( _seldate >= _sdate && _seldate <= _edate ) || _pcondition, 1, 0 )
3. Select the visual and apply a visual-level filter with the condition (Flag is 1)
If the above one can't help you get the expected result, could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi,
Its not working. flag 1 doesnot give any results.
I have already attached pbix file, can you review the same and help me with simple way to get both filters working for my desired result.
Hi @ankurbajaj07 ,
I tried to access your shared file, but it failed. It seems that I didn't have the sufficient privilege to access it. Could you please grant me the proper access to it? Thank you.
Best Regards
Sorry for that. access granted to below link for sample pbix
https://drive.google.com/drive/folders/1FDg8c1Cy_VsEo14Bgc6b9TKOpyZi1S9u?usp=share_link
Also, I tried this code
Can anyone help me with the solution?
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |