The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
)
)
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
36 | |
14 | |
12 | |
7 | |
7 |