Limit import of data from Analysis Services via DAX expression

[Cmoore]

Hi,

I'm connecting Power BI Desktop to an Analysis Services cube. I want to limit the amount of data I import to make it more efficient. The data goes back some 25 years or so but I'm just interested in the last 3 years of data. I have written a DAX expression to apply some filters to the data. Please see below;

EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
WorkingFeeEarner[Name],
"Input Local Value", [Local Rate Amount_Time]
),
WorkingFeeEarner[CurrTitleGroupName] = "Partner",
Matter[Type] = "Chargeable"
)

I want to further limit the data based on an invoice date. This data is stored as text in source. Would you happen to know how I can filter the data on date in a script like the above?

Many thanks

Chris

[v-yadongf-msft · Accepted solution]

Hi @Cmoore ,

Maybe you can try:

CALCULATETABLE (
SUMMARIZECOLUMNS (
WorkingFeeEarner[Name],
"Input Local Value", [Local Rate Amount_Time]
),
WorkingFeeEarner[CurrTitleGroupName] = "Partner",
Matter[Type] = "Chargeable",
YEAR([Date]) <= YEAR(TODAY()) && YEAR([Date]) >= YEAR(TODAY())-3
)

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.