Improve on an existing solution using multiple calculatetable functions for filtering the data

[akhaliq7]

Using dax I want to filter my dataset I have the following 2 calculate table functions I find this quite cluttering in my data model and would like to have just one calculatetable or another method if possible.

Original excel file brought into power bi whereby there are dates in the future with nulls in the values columns so created a calculatetable to filter those out below is the code:

Sales CT = 
CALCULATETABLE(
    'Sales',
    ( NOT ISBLANK('Sales'[amount]) ) ||
    ( NOT ISBLANK('Sales'[profit]) )
)

I then added a calculated column to this table as I wanted the last 12 months of data

Last12Months = 
DATEDIFF('Sales CT'[Date], TODAY(), MONTH) <= 12

p.s cannot add last 12 months calc col to first table as that had dates that were way in the future only after being filtered with the not is blank can i add in the last 12 months col.

I then created another calculatetable where I set the last12months to true this was to get the last 12 months of data only.

Sales CT 2 = 
CALCULATETABLE(
    'Sales CT',
    'Sales CT'[Last12Months] = TRUE()
)

After solving this problem my model was really cluttered and I thought there must be a simpler way to solve this problem,

[FreemanZ · Accepted solution]

hi @akhaliq7 

try like:

Sales CT =
CALCULATETABLE(
    'Sales',
    ( NOT ISBLANK('Sales'[amount]) ) ||  ( NOT ISBLANK('Sales'[profit]) ),
    DATEDIFF('Sales'[Date], TODAY(), MONTH) <= 12
)

[akhaliq7]

nothing is being filtered

[FreemanZ]

hi @akhaliq7 

Could you provide some sample data?

[akhaliq7]

I got it to work I used a part of your formula that is why i accepted your solution, the following worked for me:

CT DateDiff = 
CALCULATETABLE(
    FILTER(
        CALCULATETABLE(
            'Sales',
            NOT( ISBLANK( 'Sales'[Amount] ) ) || NOT( ISBLANK( 'Sales'[Profit] ) )
        ),
        DATEDIFF(
            'Sales'[Date],
            MAX('Sales'[Date]),
            MONTH
        ) < 12
    )
)