Forum Discussion
MTD conditional filters
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
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
Hey tex628
Nice work, your solution works like a charm.
A secondary question is in relation to YTD. I am trying to dispay the YTD budget up until the month that is current without applying filters to the report page that stipulates the current month (trying to make the model as user friendly as possible).
I've tried to modify your MTD solution to an equivalent YTD but can't get it to work. Basically I have monthly sales values for each of the 12 months of the year, I would like my variance to budget calculation to use and display the YTD budget up to the current month but without a report filter it will only display the full 12 months budget.
Any suggestions would be appreciated.
Try something along the lines of this:
Budget YTD = VAR tday = TODAY() VAR Breakpoint = DATE(YEAR(TODAY()), 1, 1) Return CALCULATE( [Total Budget] , FILTER('Calendar' , 'Calendar'[Date] >= Breakpoint && 'Calendar'[Date] <= TODAY()) )
Let me know how it goes!