Forum Discussion
Helper ITotal Budgets for Next Month
Resident RockstarWhile 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 () )
)
)
Helper IHi 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:
- 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:
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.
Anything I have to do else?
- PreetiSahu321
Thank you😊