Dynamic monthly budget calculation based on dates selected

[S_M]

Hi, 

I am facing some troubles putting together a measure that would dynamically calculate budgets based on a date filter selected. For context, i have a budget table that looks like : 

Which is also related to a date table (by the column month) : 

I want to be able to create a table that i can then slice by Daily / Monthly / Week to Date & Month to Date and the measures for 'targets' would be sliced accordingly by Daily (total monthly target from the target sheet divided by days in the month), Week to Date (total monthly target from target sheet divided by (daily target * WTD) and similar for Month to Date. 

Would anyone have a solution around this as i am stumped on how to achieve this, any help would be much appreciated! 

Thanks! 

[v-yulgu-msft]

Hi @S_M,

You need to create multiple separate measures to calculate target revenue for daily, monthly, week to date and month to date. Then, take slicer selection into account, combine these four measures into a final measure using conditional statement. Like below:

Measure = IF(LASTNONBLANK('OtherTable'[MeasureOption],1)="measure1",[Measure1], IF(LASTNONBLANK('OtherTable'[MeasureOption],1)="measure2",[Measure2],[Measure3]))

Reference: How to show dynamic measures on a chart using the slicer option.
Measures on Rows – Here is How I did it

By the way, how did you create relationships between budget table and date table? You said it is based on the column month, however, it looks like a many to many relationship.

Regards,
Yuliana Gu

[S_M]

Thanks @v-yulgu-msft, im going to give that a try. 

Also, in order to create a relationship between the date table and the budget table, i created an intermediate 'filter table', which has only a single column - distinct values for 'month'. I then related the filter table with the date and the budget tables, creating an indirect relationship between budget and date tables, that works in 'both' directions, and voila 🙂 

[v-yulgu-msft]

Hi @S_M,

Have you worked it out? If yes, please kindly mark the helpful reply as an answer. If you still have any concern, please feel free to ask.

Regards,
Yuliana Gu