Budget - Time and Date Dax Calculation

DAX DAX Calculations
1

Hi , I am doing a budget in Power BI .
At the moment I have this data come in from excel .
Below is Budget :
image

I am trying to get this to Daily sales in my Table .
image

Because the date is only 1 date in the Budget Table as below .
image

I am not sure how to I write a Dax to get the Sales per Day for each day to the entire month .

Please Advise.

2

Hi @Aree

Can you share some insight on what’s the logic to get daily sales as I can’t understand how are you achieving Sales of 142,764 on 1/7/2019, 162765 on 2/7/2019 and So-On

Thanks
Ankit J

3

Hi @ Aree

It’s good to see you here. I would recommend that you watch this video by sam ans it shows in detail how you can achieve your desired results with different granularities.

Budgeting Intro

Thanks and best regards

Najah

4

Hi @ankit ,

Thank for your reply , its a the sale that we make daily . So on the day 1st july we made $142K for the day and 2nd July we made $162K for the day .
What I am trying to do here is get an alert if the daily sales has been achieve .

So lets say daily sales is $142k and daily budget is 152K – we have not achieved the sales for the day
and 2nd day is $162K and daily budget is $152K we have achived the daily target.
This is a request from the board member , not sure that the reason .

at the moment on the budget data , I only have Monthly Budget figure . total trading days for the month . Example July 19 budget is 5,263,565 and trading day is 23 = when I divide I will get a daily budget right with is $228,851 , but whn I drop it in the table . I am only getting it for 1 day . which is for all the 1st day of the month .

I would like to get same budget figure after divide it for all the days .

5

@Aree
pls try this budget allocation formula

Budget Allocation =
VAR DaysinContext = COUNTROWS( Dates )
VAR DaysinMonth = CALCULATE( COUNTROWS( Dates ), ALL( Dates ), VALUES( Dates[Month & Year] ) )
VAR MonthlyBudgetAmounts = CALCULATE( [Total Budgets], FILTER( ALL( ‘Product Budgets’[MonthName] ) , CurrentMonth = ‘Product Budgets’[MonthName] ) )

RETURN

IF( OR( HASONEVALUE( Dates[Date] ), HASONEVALUE( Dates[Month & Year] ) ),

DIVIDE( DaysinContext, DaysinMonth, 0 ) * MonthlyBudgetAmounts,

[Total Budgets] )

the above dax will split up your monthly budget into daily budget when you drop it in your table

once you have the daily budget you can do the rest of your calculation

Sam has a great video on this out there - Daily Budget allocation…

pls try the above dax formula and let me know if you still have issues. thank you

6

Hi @NajahS, Thanks let me try . Will update you

7

HI @C9411010 , thanks for your reply.

I did see this video , prior to post .
Sam is working with the yearly figure and dividing by monthly days and 366.

I think im missing a point . I have different budget for different month .
My working day is working fine - as it exclude working and public holiday . So I have total days of 252 a year and correct if drop in montly table.

Can I use a calculate and filter to give the same result for each month ?

image
image323×645 22 KB

8

Hi @Aree

  1. Assuming you already have a Calendar table else you can create on like below.

http://portal.enterprisedna.co/courses/303649/lectures/4673891

  1. Ensure there is no relationship present b/w Date Table and Calendar Table.

  2. Create below measures.
    i) [Year] field in Budget table in Date format.

    TotalBudgetSales =
    CALCULATE (
    SUM ( Budget[Sales] ),
    TREATAS ( STARTOFMONTH ( ‘Calendar’[Date] ), Budget[Year] )
    )

ii) [Year] field in Budget table in Text format like “Jul-19”. First create a new column in Date Table

Month-Year = FORMAT('Calendar'[Date],"mmm-yy")

Then create a Measure like below.
TotalBudgetSalesPerDay =
CALCULATE (
SUM ( Budget[Sales Per Day] ),
TREATAS ( VALUES ( ‘Calendar’[Month-Year] ), Budget[Year] )
)

If issue is still not resolved, suggest to share a sample PBIX file.

Thanks
Ankit J

9

HI @ankit Tried to so it but im only getting the full yearly figure . I am looking for Daily sales figure in all the date for the month and so on . I will share the pbix file in abit.

If i take the relation ship out ( delete) is throwing off my cumulative budget for the year .

10

Hi @ankit ankit
have attached the pbix file here. Sales_Budget Module v9.pbix (2.2 MB)

Please advise.

11

Hi @Aree

I am able to get the desired result by making suggested changes.

image

  1. Removed relationship b/w Date table and Budget Table.

  2. Changed the DAX formula as below.

    Bdgt Sales/Day =
    CALCULATE (
    SUM ( Budget[Sales Per Day] ),
    TREATAS ( STARTOFMONTH ( Dates[Date] ), Budget[Year] )
    )

  3. Alternatively from Point 2, Create a new column “MonthStartDate” in Date table and use that in Treatas

    MonthStartDate =
    STARTOFMONTH ( Dates[Date] )

    Sales Budget =
    CALCULATE (
    SUM ( Budget[Sales Budget- x] ),
    TREATAS ( VALUES ( Dates[MonthStartDate] ), Budget[Year] )
    )

Attached the PBIX file. Sales_Budget Module v9_EDNA Solution.pbix (2.2 MB)

12

Thank you so much @ankit.