Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 11, 2024

In very first visual you have months and some measures. One of measures correctly shows number of days in each month.

If you'd like to calculate number of days in the month (still will be 30 or 31) and aggregate them for each item in filter context, it could be like

noofdays = COUNTROWS(Calendar_) * COUNTROWS('Points')

If I understood your logic correctly.

Excellove15's avatar
Excellove15
Iron Contributor
Dec 12, 2024

Hi Sir,

 

Thanks for your quick response!🙂

This dax works like a gem and I have implemented this in another dax called 'Forecast Budget Unit (TimeSeries)'

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 = COUNTROWS(Calendar_) * COUNTROWS(Points)
        VAR _nofinvdays = COUNTROWS(DataInvoice)
        VAR _missing = _noofdays - _nofinvdays
        RETURN
        IF(
            _missing = _noofdays,
            _monthtarget,
            _target * _missing
        )
    )
)

When I used it in visual, the aggregate shows different value 6,139.11 instead of aggregating to 1,665.07.

PFA file here https://1drv.ms/u/c/cfada767f73d87ed/EfvGy8TkX0BKuMEB5gLbJk4BsgJn2ynb3hzZdjPgAygwig?e=k47lQk

Please advise!

 

Thanks in advance!

SergeiBaklan 

Resources