Hi there,
Feel like I’m missing something obvious. I have a table that would provide a budgeted value, frequency and start date. Something like:
|Item|Amount|Frequency|Start Date|
|Budget Item A|100|Weekly|1/01/2019|
|Budget Item B|200|Monthly|2/03/2019|
|Budget Item C|300|Quarterly|1/06/2019|
I would like to be able to use DAX to calculate the cost as it would fall into each financial period. So for example, if you drop the amount into a visual, we would see $100 appear every 7 days from 1/1/19, $200 appear on the 2nd of each month and so on.
I feel like this should involve a SUMX across a dates table + DATEADD but I can’t quite get it across the line. Any ideas?
Thanks in advance.
KCinMelbourne
This is a very human way to think of things. You will have to unwind this in complex DAX code every time you touch it in your calculations. Consider using Power Query (or your source database) to enter all the amounts weekly in a base table. The concept of “start date” goes away, because when you start entering amounts, that’s when that budget item starts. That sort of table really lends itself to being incorporated into a model at weekly granularity and makes writing DAX measures that involve budget dead simple. The question is where do you pay the price of complexity…I think its “cheaper” to pay it up front instead of entering the budget information in “shorthand” and having to unwind it in DAX before you can use it.