Want to Show measures dynamically with time

[AshDil]

Hi,
I have 3 tables, one is having sales, other is having budget and another is having forcast values.

Table - 1: Sales table

Table - 2 : Budget table

Table - 3 : Forcast table

I want to use two matrix tables one for months and other for quarters

In first matrix table: (which is for Months)

I want show sale values for completed months i.e, Jan,Feb,Mar,Apr,May,Jun.

For the current month i.e, Jul, need to show MTD, forecast values

For the months Aug and Sep need to show forecast values.

As time continues, If we are in Aug then we need to show sale values for Jan,Feb,Mar,Apr,May,Jun and Jul

Then the current month is Aug, So for Aug month need to show MTD, forecast values.

Likewise every time it should be dynamic, when we are entering new months.

In second matrix table : (which is for Quarters)

I want to show sale values and budget values for completed quarters i.e, Q1,Q2

for current quarter i.e,Q3, need to show forecast values and forecast vs budget.

If we have moved to Q4, 

need to show sale values and budget values for  Q1,Q2,Q3

for Q4 need to show forecast and forecast vs budget.

Likewise it should be dynamic when we are moving to new Quarters.

Please help me to do.

Thanks,

AshDil.

[amitchandak]

@AshDil , First of all unpivot the budget table (month)

https://radacad.com/pivot-and-unpivot-with-power-bi

Now with help from  mon the and  year create date , a new column

 date = "01-" & [Month] & "-" [Year] // change data type to year

Join all three tables with date table having qtr year etc

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Create a common category table and use that too

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

then analyze with help from date and category  table

[AshDil]

Hi @amitchandak ,

I have done above all steps but my requirement is to show measures dynamically with time. Please help me on that.

Thanks,

AshDil.