Dax measure not considering the filter context

[sivarajan21]

Hi,

I have created below dax:

Forecast Budget Unit (TimeSeries) = 
SUMX(
    VALUES( Calendar_[Month-Year] ),
    SUMX(
        VALUES( Points[DBName-Point_Id] ),
        VAR  _target = CALCULATE(
            AVERAGE( TargetTimeSeriesMonth[Dail Units] ),
            TargetTimeSeriesMonth[TargetType] = 1
        )
        VAR  _monthtarget = CALCULATE(
            SUM( TargetTimeSeriesMonth[Usage] ),
            TargetTimeSeriesMonth[TargetType] = 1
        )
        VAR  _noofdays = CALCULATE(
                COUNTROWS( Calendar_ ) * COUNTROWS( Points ),
                KEEPFILTERS( Points )
            )
        VAR  _nofinvdays = CALCULATE(
            COUNTROWS( DataInvoice ),
            KEEPFILTERS( Points )
            )
        VAR  _missing = _noofdays - _nofinvdays
        VAR  Result =
        IF(
            _missing = _noofdays,
            _monthtarget,
            _target * _missing
        )
        RETURN Result
    )

When I tested Dax measure, I found the below in the visual:

I took the variables from Forecast Budget Unit (TimeSeries) dax and created 3 new separate measures to test it.

Found that missing dax is in negative (-62) and noofdays dax contains 31 days. This makes me doubt that I made some mistake in dax of noofdays. I am struggling to figure it out.

My requirement for :

noofdays dax - how many days are there in the given month-year for each point i.e at a point level

nofinvdays dax- how many invoice days are there for the given month year and point

missing dax - therefore how many days do i have missing

For example, April 2024 has 30 days,and a particular point might have 20 days in the invoice. so 30-20=10 missing days for that point in that month

therefore 10*average forecast rate(_target variable in above measure) = your forecast budget Unit (TimeSeries).

Above 3 dax variables form integral part of Forecast Budget Unit (TimeSeries). Could you please help me with logic?

PFA file here Portfolio Performance - v2.15 (1).pbix

Thanks in advance!

@marcorusso @Greg_Deckler @Ahmedx 

[v-xinruzhu-msft · Accepted solution]

Hi @sivarajan21 

Based on your data, i am confused that in the first row , if the nofinvdays larger  than the days in july, should it return 0 because there are no missing days in that month.

and if you wan to the missing days >0, you can modify it to the following.

missing = abs([noofdays] - [nofinvdays])

Best Regards!

Yolo Zhu

[sivarajan21]

Thanks @v-xinruzhu-msft 

Its about the distincount of points for the noofdays measure and it works