Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
hi @akhaliq7
try like:
Sales CT =
CALCULATETABLE(
'Sales',
( NOT ISBLANK('Sales'[amount]) ) || ( NOT ISBLANK('Sales'[profit]) ),
DATEDIFF('Sales'[Date], TODAY(), MONTH) <= 12
)
hi @akhaliq7
try like:
Sales CT =
CALCULATETABLE(
'Sales',
( NOT ISBLANK('Sales'[amount]) ) || ( NOT ISBLANK('Sales'[profit]) ),
DATEDIFF('Sales'[Date], TODAY(), MONTH) <= 12
)
nothing is being filtered
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
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |