Filtering dataset to a certain date values in PBI DAX-CodePudding

I have the following table tbl_Homeless , with [# Homeless] measure:

I need to add a filter or to modify my measure, so that it could be only shown the data where [Month] is at least within 12 months period or more. In the case shown on a picture it should be only data in blue. Data that are non marked in blue (ClientID = 44) needed to be filtered out. Because the [Month] of those data is less than 12 Months period.

After applying a filter to [# Homeless] measure, I expect to have the following dataset (without data where ClientID = 44):

My code should be something like:

updated_Homeless = Calculate([# Homeless], Filter....

But I don't know how to apply filter for my [Month] field

CodePudding user response：

Why not try this? Please run the code to check the result.

updated_Homeless_Measure =
VAR LastDate =
    MAX ( tbl_Homeless[Month] )
VAR Result =
    CALCULATE (
        [# Homeless],
        DATESINPERIOD ( tbl_Homeless[Month], LastDate, -12, MONTH )
    )
RETURN
    Result

But I think You expect your DAX code to return a table, not a scalar values as a measure does. So Create "New Table" command, then paste this code, It returns a table:

updated_Homeless_Table =
VAR LastDate =
    MAX ( tbl_Homeless[Month] )
VAR Result =
    CALCULATETABLE (
        tbl_Homeless,
        DATESINPERIOD ( tbl_Homeless[Month], LastDate, -12, MONTH )
    )
RETURN
    Result