Skip to main content
cancel
Showing results for 
Search instead 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

Reply

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 
logic need to be cracked.png

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1669370109418.png

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. 

View solution in original post

5 REPLIES 5

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
Community Support
Community Support

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.

vtangjiemsft_0-1669370109418.png

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
Solution Sage
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
// 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

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 

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.

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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