Total Budgets for Next Month

[PreetiSahu321]

Hello Power BI Community Members,

I have a Budget list which is having all the Budget Dates (Data type as Date and Time) and Budget Amount (data type as the Whole number). As per my requirement, I want to show the total budget amount for next month. You can see my Budget table which looks like below screenshot:

Let me explain to you properly so that you can understand easily. In my Budget list, the Budget date is having all the dates like 10/30/2019, 11/25/2019, 12/07/2019, 01/05/2020, 02/10/2020 and so on.
As my current month is October, So I want to show the total Budget amount for November.

Also, I tried an approach with the NEXTMONTH function as:
Total Budget in next month = CALCULATE(SUM(BudgetsNew[BUD_AMOUNT]),NEXTMONTH(BudgetsNew[BUD_MONTH_YEAR]))

But I did not get any results.

As I am new to Power BI, I don't have much more knowledge about Power BI DAX. Please, Can anyone suggest to me how I can do it easily?

Thanks in Advance!!!

[sturlaws]

Hi @PreetiSahu321 

how to do that will depend on the format of your BudgetsNew[BUD_MONTH_YEAR], or any other time columns you have. If you can paste some data from you model(not screen shots), or create sample report and upload it to dropbox/onedrive/other, it will be possible to help you.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[PreetiSahu321]

Hi @sturlaws,

This BudgetsNew[BUD_MONTH_YEAR] is in the below format:

"MM/DD/YYYY" 

And below attached is my Budgets table where the next month's formula is not working.

https://www.dropbox.com/s/vtirm2681xi891g/Budgets.pbix?dl=0

Please, can you suggest to me what I have to do?

[sturlaws]

Great!

try this measure

Next Month Budget2 = CALCULATE(SUM(Budgets[BUD_AMOUNT]); PARALLELPERIOD(Budgets[BUD_MONTH_YEAR];1;MONTH))

hm, Budgets[BUD_MONTH_YEAR] has a timepart, some of the items is at 07:00, others at 08:00, not sure if that might cause some issues down the road. I would separate that into a date and time column(if time is at all needed).

Cheers,
Sturla 

[PreetiSahu321]

Hi @sturlaws ,

As I have put this measure formula, still it is not showing me the exact value. As this month is October, It should display the total value of November i.e. 25,300 as you can see in the below screenshot.

Anything I have to do else?

[sturlaws]

Sorry, I thought you wanted to have budget next month in a table with month on the axis.

If you just want the next month relative to todays date, use this formula

Next Month Budget =
CALCULATE (
    SUM ( Budgets[BUD_AMOUNT] );
    FILTER (
        ALL ( Budgets[BudgetDate] );
        Budgets[BudgetDate]
            = DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) + 1; 1 )
    )
)

But you need to separate the date part of [BUD_MONTH_YEAR], you can do this by creating a new column like this

BudgetDate = Budgets[BUD_MONTH_YEAR].[Date]

[PreetiSahu321]

Hi @sturlaws ,

Now, this measure formula is working perfectly. Thank you so much😊

One more thing I want to know is, Can you tell me how I can get the total Budget of the current month? Means, As this current month is October, So it will show me only the total Budget of October.

If you have any idea, please suggest to me what I have to do?

[sturlaws]

Current Month Budget =
CALCULATE (
    SUM ( Budgets[BUD_AMOUNT] );
    FILTER (
        ALL ( Budgets[BudgetDate] );
        Budgets[BudgetDate]
            = DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) ; 1 )
    )
)

[PreetiSahu321]

Hi @sturlaws ,

When I have tried this current month formula in my Budget List, It is working perfectly and it gives me the exact value of October.

Similarly, I have another List as "Orders". Here also, I want to get the total order of the current month. But, When I am trying this formula in this Orders List, then it is not working and displaying the wrong value as you can see in the below screenshot:

Instead of displaying 50K, It is showing me the wrong value as 22K. Here, the Net value is the amount (Whole number) and another column I have created as Order Date as you said to separate the Date.

[sturlaws]

While your budget was a monthly value, I am guessing your orders are daily values. So what you are doing now is that your are looking at orders from the October 1.

If you don't have it already, you should create a data dimension/table, and create a relationship between your orders-table and date table using order date.

Then change the measure to this:

Current Month Net value =
CALCULATE (
    SUM ( Orders[Net value] );
    FILTER (
        ALL ( dateTable );
        dateTable[MonthNumber] = MONTH ( TODAY () )
            && dateTable[year] = YEAR ( TODAY () )
    )
)

[PreetiSahu321]

Hi @sturlaws ,

As you told, I have created one Date table by using this below formula:

Then I have made a relationship with that Date table with the Orders list and changed the measure as you per your given formula. But still, it is not showing me the exact value.

[sturlaws]

Your code looks ok, I can't see why it does not work. Have you created the relationship on the correct date field?

I created a little demo report, and it works fint there.

[PreetiSahu321]

This below is my Orders report. Here, I have put the same formula which you have put in your report. But, still, it did not work for me even not giving any error also.

https://www.dropbox.com/s/0zeor4rglbsmyot/Orders.pbix?dl=0

Please, Suggest to me what I have to do else?

[sturlaws]

It's because you have not created a relationship between the two tables. First format 'Orders'[Created] as date, then drag 'Orders'[Created] onto vDate[Date]

You might want to have a look at these tutorials:
https://docs.microsoft.com/nb-no/learn/modules/model-data-power-bi/

[PreetiSahu321]

I have made the relationship between the Orders and Date table. In the Orders table, "Req.dlv.dt" is my Order date column instead of Created. Even after making the relationship between two tables, it is not working.

Below is my attached report which is not working:

https://www.dropbox.com/s/0zeor4rglbsmyot/Orders.pbix?dl=0

[sturlaws]

I can't understand what is happening here, I tried formatting [Req.dlv.dt] and [date] to date format but it did not work. Then I did this:

Column = DATE(YEAR(Orders[Req.dlv.dt]);MONTH([Req.dlv.dt]);DAY(Orders[Req.dlv.dt]))

and join between [date] and [Column], and then it works.

No idea why this happens. Possibly go into Power Query and do the date format conversion in there

[PreetiSahu321]

Anyhow, It is working now. Thank you very much😊

[PreetiSahu321]

HI @sturlaws ,

I need one more help. Similarly, I have another list as Sales which is having all the Bill Date(Data type as Date and Time) and Total Product (Data type as the Whole number). As per my requirement, I want to show the total product sales for next month. As you said previously, To get the total product sales for next month, I put the below formula which is not working for me:

Next Month Sales = CALCULATE(SUM(Sales[TotalProduct]), PARALLELPERIOD(Sales[BillDate],1,MONTH))

Also, you can see my below-attached Sales report:

Sales Report 

Please suggest me what I have to do?

[sturlaws]

1. Create a date table

2. format BillDate to datatype Date

3. Connect date table and sales table on 'date'[date] 1:* 'Sales'[BillDate], just like you did for orders.

I reckon you have the sales and orders tables in the same model, it is most likely sufficient with 1 common date table

[PreetiSahu321]

Hi @sturlaws ,

I did the same what you have told those points. I have created one Date table, Changed the Bill date to Date format and also joined the 'Sales'[BillDate] to 'date'[date]. But still, it is showing me the same value.

Sales Report 

Anything I have to do else?

[sturlaws]

https://www.dropbox.com/s/6um2as497kd11sg/Next%20Month%20Sales.pbix?dl=0