DAX Issue - Filter within calculate formula

[Anonymous]

Hi,

Hoping someone can help. I have the below measure set up in my Power BI report - 

YTD_Actual:=IF(ISFILTERED(Reporting_Periods[Year]),calculate(sum([VALUE]),filter(DWH_FACT_MAIN,[YearPeriodNumber] <= [MaxYearRP] && [TRANTYPE] ="ACTUALS"),ALLEXCEPT(Reporting_Periods,Reporting_Periods[YEAR])),BLANK())

I need to change this so that the filter reads > rather than >=, however when i have updated this it doesn't bring any data back whatsoever. It works fine with the <= though which i am struggling to understand why/how?

For information, YearPeriodNumber is a Sum column whereas MaxYearRP is a set up as a measure. In case that makes a difference.

Also, Year Period comes from the table DWH_Fact_Main whereas MaxYearRP is in another table called Reporting_Periods.

Any help is much appreciated.

Scott 

[Pragati11]

Hi @Anonymous ,

This is not helping much. If the condition change in your DAX is not returning any data, then there are not data rows for this condition.

Also, try sharing more details like sample data and screenshots:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump-to/first-unread-message

Thanks,

Pragati

[Anonymous]

Hi,

There is definitely data there to be brought back, otherwise the <= wouldn't be working either. 

So using <= currently brings back Periods 1-4, with 4 being the 'current' period. I want to bring back anything from closed periods i.e. before period 4, therefore need to change the logic to from <= to just <.

Please see attached screenshots before i make the change i.e. using <= and then after i make the change i.e. using <. You can see the charts and cards appear blank. It also affects some matrix tables i also use this field in.

[mahoneypat · Accepted solution]

Pull your MaxYearRP measure in the FILTER out into a variable up front.  Using it there will trigger context transition and each period will be compared to see if it is less than itself which will return no rows.

YTD_Actual :=
VAR maxRP = [MaxYearRP]
RETURN
    IF (
        ISFILTERED ( Reporting_Periods[Year] ),
        CALCULATE (
            SUM ( [VALUE] ),
            FILTER (
                DWH_FACT_MAIN,
                [YearPeriodNumber] < maxRP
                    && [TRANTYPE] = "ACTUALS"
            ),
            ALLEXCEPT (
                Reporting_Periods,
                Reporting_Periods[YEAR]
            )
        ),
        BLANK ()
    )

Regards,

Pat