Forum Discussion
MTD conditional filters
Hi
I’m hoping the more knowledgeable of you might be able to help me with a challenge I’m struggling to resolve with MTD conditional filters.
I have a series of transactions which for simplicity sake consist of the transaction date, Value ($) and transaction type (two character code).
The challenge I have is that I need to include all transactions for historic reporting periods (prior to the current month) but exclude transactions with a specific type code during the current MTD. Preferably this would need to be accommodated within a single DAX measure otherwise I’m going to have to develop a series of reports for all historical transactions (including all transactions) with a separate series of reports for current MTD & YTD.
If anyone could share some thoughts it would be greatly appreciated.
Thanks.
Marcus
No wonder you had problems 🙂 My measures didn't work!
But i've attached a file with some measure that are actually working as I intended, take a look and see if we meet the requirements.
/ J
6 Replies
Marcus_E , Not sure I got it with date table
Data before 1 year
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))
Data 1 month Back
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))))
before this year
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date]) && date[date]< Date(year(today),1,1)))
MTD
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090eCould this work for you?
MTD = VAR tday = TODAY() VAR Breakpoint = DATE(YEAR(TODAY(), MONTH(TODAY() , 1) Return CALCULATE ( [Total Sales] , Calendar[Date] >= Breakpoint , Facts[TransactionType] <> "XX" )Historic = VAR tday = TODAY() VAR Breakpoint = DATE(YEAR(TODAY(), MONTH(TODAY() , 1) Return CALCULATE ( [Total Sales] , Calendar[Date] < Breakpoint )Total = [MTD] + [Historic]Thanks tex628 I'm having trouble translating your solution into something that works for my dataset. Unfortunately I cannot upload a sample pbix file so I've attached a small sample of example data in the table below.
Asset Trans_Date Trans_value Trans_type 11000 01-Apr-20 $12,382.50 11000 01-Apr-20 -$3,540.00 RE 11000 01-Apr-20 $3,600.00 WE 11000 01-May-20 $8,937.50 11000 01-May-20 $608.40 WA 11000 01-Jun-20 $9,110.00 11000 01-Jul-20 $8,645.00 11001 01-Apr-20 $106,837.50 11001 01-Apr-20 -$217.70 KP 11001 01-Apr-20 -$3,915.00 SB 11001 01-Apr-20 $0.00 SC 11001 01-Apr-20 $407,564.70 WA 11001 01-Apr-20 $17,191.90 WE 11001 01-May-20 $79,245.00 11001 01-May-20 -$10,680.60 SB 11001 01-May-20 $0.00 SC 11001 01-May-20 $27,996.00 WA 11001 01-May-20 $9,560.30 WE 11001 01-Jun-20 $24,467.50 11001 01-Jun-20 -$176.30 KP 11001 01-Jun-20 -$14,575.30 SB 11001 01-Jun-20 $0.00 SC 11001 01-Jun-20 $18,944.90 WA 11001 01-Jun-20 $4,687.00 WE 11001 01-Jul-20 $29,232.50 11001 01-Jul-20 $26,358.70 RE 11001 01-Jul-20 -$11,987.80 SB 11001 01-Jul-20 -$64,654.70 SC 11001 01-Jul-20 $37,336.20 WA 11001 01-Jul-20 $1,392.20 WE The ouput is very simple: I'm trying to sum the Trans_value by Asset for each month. The SUM value for all transactions prior to the current month will need to exclude any transaction type of 'SC' whereas the current MTD sum will need to include all transaction types.
Hopefully you can steer me in the right direction.
Thanks again
No wonder you had problems 🙂 My measures didn't work!
But i've attached a file with some measure that are actually working as I intended, take a look and see if we meet the requirements.
/ J
