Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
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.
Hi @chchIntech
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.
Cheers
CheenuSing
Hi @chchIntech
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.
Cheers
CheenuSing
Hi @CheenuSing
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.
Hi @chchIntech,
Do you get expected result after creating measures using the formulas CheenuSing provides?
Thanks,
Lydia Zhang
Hi Lydia @v-yuezhe-msft,
No, we didn't get the expected result with the formulas.
Any help would be much appreciated.
Thanks,
chchIntech
Hi Lydia @v-yuezhe-msft & @CheenuSing
I got the Year to Date working, but not MTD.
Your help is much appreciated.
Thanks,
chchIntech
Hi @chchIntech,
Create MonthToDate total using the following DAX and check if it returns your expected result.
MTD = CALCULATE(
SUM(bisnif[Sales Value])
,DATESMTD(MasterCalendar[Date])
)
Thanks,
Lydia Zhang
Hi Lydia @v-yuezhe-msft and @CheenuSing
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.
Thanks,
chchIntech
Hi @chchIntech,
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.
Thanks,
Lydia Zhang
Hi Lydia @v-yuezhe-msft,
Thanks for the PIBX file, the budget worked with the expected result. However, I did notice later with the MTD & YTD that the expected result is not correct and MTD gives a blank value.
For MTD I am using: MTD = CALCULATE(SUM(bisnif[Sales Value]),DATESMTD('Master Calendar'[Date].[Date]))
(throws up a blank value)
YTD = CALCULATE(SUM(bisnif[Sales Value]),DATESYTD('Master Calendar'[Date],"01-31"))
(expected result is incorrect)
Below is the relationship between the two tables (Sales file & Master Calendar).
Please suggest.
Thank you,
chchIntech
Hi @chchIntech @drodrigues,
Please use the following DAX formulas to create MTD and YTD.
Monthtodate = CALCULATE(SUM(bisnif[Sales Value]),DATESMTD('Master Calendar'[Date]))
YTDTEST = CALCULATE(SUM(bisnif[Sales Value]),DATESYTD('Master Calendar'[Date],"01-31"))
Thanks,
Lydia Zhang
Hi @chchIntech,
Could you please send me your PBIX file via Private message? You can upload PBIX file to OneDrive and send me shared link of the PBIX file.
Thanks,
Lydia Zhang
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |