Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Semi-Additive Measures - YTD (Direct Query)

I'm trying another approach and attempting to calculate the most recent customer count, which I only get once every month or so. 

Customer Count To Date = 
var __currentdate = max('UDM DateTable'[CALENDAR_DATE])

var __LNBDate = 
    calculate(
        LASTNONBLANK('UDM DateTable'[CALENDAR_DATE],
            calculate(
                COUNTROWS('UDM TableServedByDate Center')
            )
        )
        ,FILTER(
            all('UDM DateTable'[CALENDAR_DATE])
            ,'UDM DateTable'[CALENDAR_DATE] <= __currentdate
        )
    )

Return
    CALCULATE(
        [Customer Count Center]
        ,FILTER(
            ALL('UDM DateTable'[CALENDAR_DATE])
            ,'UDM DateTable'[CALENDAR_DATE] = __LNBDate
            )
        )

But it takes FOREVER. It is impossibly slow. Any recommendations?

 

Thanks in advance,

Andy

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous do you ahve cross direction filter set to BOTH. without knowing how model is setup try changing this

     

    ,FILTER(
            all('UDM DateTable'[CALENDAR_DATE])
            ,'UDM DateTable'[CALENDAR_DATE] <= __currentdate
        )

to

'UDM DateTable'[CALENDAR_DATE] <= __currentdate
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank Parry I've done that but it doesn't seem to make much of a difference. It is interesting though that usually I see the query go to the database, but I'm not seeing it in the database activity monitor now. I'm not sure what the deal is, but it's using a lot of memory and CPU on the local machine but I'm not seeing anything on the database. 

       

      The relationship does not filter in both directions because there are some days that don't have any activity, but I have to have that in the calculation because on down the line there is a comparison to a target that does accumulate on those days.