how to get Value from budget table if master calendar date match with actual sales table.

[UjjawalTyagi518]

i have two tables (1st is actual sales table and 2nd is budget planned table) 
in actual sales table we have data from JAN to SEP and in budget Table we have data from JAN to DEC
my master clender is created from Actual sales billing date.
i need to calculate next 3 months values based on conditons,
1. if we select JAN from master calender and we have (quantity)  then is should sum the next three month quantity from actual sales table).
2. if we select AUG from master calender then it should sum the value of SEP(from actual) & (OCT&NOV) from Budget Planned table.
3. if we select SEP from master calender then it should the value from the budget planned for next 3 months 

[UjjawalTyagi518]

Hi @v-tangjie-msft , @daXtreme 
thanks for the solution,
can you also help me in another logic,
which  i have share on community 

[v-tangjie-msft · Accepted solution]

Hi @UjjawalTyagi518 ,

According to your description, here are my steps you can follow as a solution.

(1)We can create a calendar table.

Calendar = ADDCOLUMNS (
CALENDAR ( date(2022,01,01),date(2022,9,30) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"MonthName",FORMAT([Date],"MMM")
)

(2)We can create a measure. 

Measure = 
var _a = MIN('Calendar'[Date])
var _b = EOMONTH(_a,0)+1
var _c = EOMONTH(_a,2)+1
var _d = MAX('Actual sales'[Billing Date])
var _sumactual = CALCULATE(SUM([Quantity]),FILTER('Actual sales',[Billing Date]>=_b&&[Billing Date]<=_c))
var _sumbudget = CALCULATE(SUM('Budget planned'[Quantity]),FILTER('Budget planned',[Billing Date]>EOMONTH(_d,0)&&[Billing Date]<=_c))
return IF(_a<=EOMONTH(_d,-3),_sumactual,_sumactual+_sumbudget)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[daXtreme]

// First, the base measures...

[Actual Total] = SUM( Sales[Quantity] )
[Planned Total] = SUM( Planned[Quantity] )

[Actual+Planned Qty] =
// MonthID is an integer that numbers
// consescutive months in your calendar.
// This is a sequence that increases by
// 1 over the entire period covered by
// your calendar. Please note that a calendar
// must always have all days in it regardless of
// whether you use them or not. Field that you
// don't need can be hidden but they must be there.
// For details on how to create a proper calendar
// for a model, please refer, for instance,
// to dax.guide/datesytd.
var CurrentMonth = SELECTEDVALUE( 'Calendar'[MonthID] )
var LastDateInSales =
    CALCULATE(
        MAX( Sales[Billing Date] ),
        REMOVEFILTERS( )
    )
var LastMonthInSales =
    CALCULATE(
        SELECTEDVALUE( 'Calendar'[MonthID] ),
        'Calendar'[Date] = LastDateInSales,
        REMOVEFILTERS( 'Calendar' )
    )
var ActualPart = 
    CALCULATE(
        [Actual Total],
        'Calendar'[MonthID] > CurrentMonth
        &&
        'Calendar'[MonthID] <= CurrentMonth + 3,
        REMOVEFILTERS( 'Calendar' )
    )
var PlannedPart = 
    CALCULATE(
        [Planned Total],
        'Calendar'[MonthID] > LastMonthInSales
        &&
        'Calendar'[MonthID] <= CurrentMonth + 3,
        REMOVEFILTERS( 'Calendar' )
    )
var Total = ActualPart + PlannedPart
return
    Total

[UjjawalTyagi518]

Hi @daXtreme 
can you tell me how you make this month id 
i am not able to crete that and implement the funtion.
thakns if possible can u send  the pbix 

[UjjawalTyagi518]

thanks alot for the answer @daXtreme 
can you please provide me the PBIX file for the same so that i can understand it much better.