cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

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

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

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Helper I

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

Community Support

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.

Solution Sage
``````// 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
// 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``````
Helper I

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

Helper I

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors