Forum Discussion

Marcus_E's avatar
Marcus_E
Helper I
6 years ago
Solved

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 simplic...
Need Help
  • tex628's avatar
    tex628
    6 years ago

    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

Marcus_E's avatar
Marcus_E
Helper I
5 years ago

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.

tex628's avatar
tex628
Community Champion
5 years ago

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!