Forum Discussion

S_M's avatar
S_M
Icon for Helper III rankHelper III
8 years ago

Dynamic monthly budget calculation based on dates selected

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 :  ...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
S_M
Icon for Helper III rankHelper III
8 years ago

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's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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