Need two filters to be applied on visual matrix table having date distribution

[ankurbajaj07]

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

[ankurbajaj07]

Hi, please find below pbix file

https://drive.google.com/file/d/1rRfzud5GX1xagIJjyPjEmj2NfB5cdMBk/view?usp=share_link

[v-yiruan-msft]

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

[ankurbajaj07]

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.

[v-yiruan-msft]

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

[ankurbajaj07]

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

Last3MonthsAndLastFYMarDataDistribution =

VAR CurrentDate = TODAY()

VAR Last3MonthsStartDate = DATEADD(MAX('Main Data table'[Date]), -2, MONTH)

VAR Last3MonthsEndDate = MAX('Main Data table'[Date])

VAR CurrentYear = YEAR(TODAY())

VAR LastFiscalYear = IF(MONTH(TODAY()) > 3, CurrentYear, CurrentYear - 1)

VAR LastFYMarStartDate = DATE(LastFiscalYear - 1, 4, 1)

VAR LastFYMarEndDate = DATE(LastFiscalYear, 3, 31)

RETURN

    CALCULATE(

        SUM('Main Data table'[Date]),

        FILTER(

            'Main Data table',

            'Main Data table'[Date] >= Last3MonthsStartDate && 'Main Data table'[Date] <= Last3MonthsEndDate

            || 'Main Data table'[Date] >= LastFYMarStartDate && 'Main Data table'[Date] <= LastFYMarEndDate

        )

    )

Will it work? actually i am facing one error -> The first argument to 'DATEADD' must specify a column.

i am not understanding the error , so can you help me with the error and can check and confirm whether this will work?

[ankurbajaj07]

Can anyone help me with the solution?