I am relatively new to Power BI, and still learning some of the DAX capabilities.
I am trying to create a report for our client that shows the following:
The values come out of an Invoice summary file, that shows the value of the Invoice by calendar date. So there can be multiple entries of Invoices for February, multiple for March and multiple for April etc...
The Invoice extract data we have will update daily with new info.
The Monthly Budgets are fixed to a calendar month, and the yearly budget is fixed for that financial year.
My questions are:
If the data updates itself daily and time moves on - how do I get the Current Month values to recognise the current month and total them accordingly? i.e. I was in March, but now it is April - how do I get it to show only current April sales without remaking the report?
The financial year begins in February (for this client). How do I get it to add the budget amounts of FEB, MAR and APRIL once we reach April? What happens when it becomes May?
Many thanks in advance for your help.... New to this stuff, but looking forward to getting into deeper.
1. What is your data model like- how many tables do you have in the model, I assume you have a invoices data file and also a budget file.
2. Do you have a master calendar table based on the invoices date.
3. What is the relationship between the tables.
4. For Month to Date you can use TOTALMTD function like
MonthToDateTotal = TOTALMTD( ( [SalesAmount]), ( MasterCalendar[Date] ) )
5. For Year to Date you can use like
YearToDateTotals = CALCULATE( SUM( Sales[Tran_Amount] ), DATESYTD( MasterCalendar[Date],"01-31" ) ), where 01-31 incidates that the financial year ends Jan 31.
Please share some sample data to find an appropriate solution.
Please download April 2017 update of power BI desktop and it is making it easier for all of us to build new measures very easily.
Watch the videos associated with the update.
Thanks for the quick response & apologies for the delay. To answer your questions:
1. We are using .CSV with three files, budget file, invoice file & master calendar.
2. Yes, have a master calendar
3. Relationship is Many to one (*:1), Cross filter direction: Both
Sample data below:
Relationship: invoice file (confirmed date) & master calendar date
Thank you for all the help. Hoping to find a solution.
Do you get expected result after creating measures using the formulas CheenuSing provides?
Create MonthToDate total using the following DAX and check if it returns your expected result.
MTD = CALCULATE(
Thank you very much for the help and responses, the YTD & MTD worked like a breeze. However, we still couldn't get the Monthly Budget & Yearly Budget to do the same.
As time moves on, we need the monthly budget to reflect the current month budget and the Yearly Budget to Date to reflect from the start of the financial year (i.e. Feb) to the current month (Feb+March+April).
Below are the screenshots of the budgets & budget table (calculations are incorrect).
Please share your thoughts and suggestions.
Which specific data do you want to get based on the above budget table? Which logic do you use to calculate the result? Any relationship exists between the budget table and other tables?
I make a simple test using my sample data, check if the PBIX file returns your expected result.