cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chchIntech Frequent Visitor
Frequent Visitor

How do I create a Month to Date and Budget total by calendar date?

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:

PowerBI query.PNG

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.

13 REPLIES 13
Highlighted
Super User
Super User

Re: How do I create a Month to Date and Budget total by calendar date?

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Super User
Super User

Re: How do I create a Month to Date and Budget total by calendar date?

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
chchIntech Frequent Visitor
Frequent Visitor

Re: How do I create a Month to Date and Budget total by calendar date?

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

 

relationship.JPGbudget.JPG

 

 

 

Thank you for all the help. Hoping to find a solution.

 

 

 

 

 

Moderator v-yuezhe-msft
Moderator

Re: How do I create a Month to Date and Budget total by calendar date?

Hi @chchIntech,

Do you get expected result after creating measures using the formulas CheenuSing provides?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chchIntech Frequent Visitor
Frequent Visitor

Re: How do I create a Month to Date and Budget total by calendar date?

Hi Lydia @v-yuezhe-msft,

 

No, we didn't get the expected result with the formulas. 

 

 

Any help would be much appreciated.

 

Thanks,

chchIntech

chchIntech Frequent Visitor
Frequent Visitor

Re: How do I create a Month to Date and Budget total by calendar date?

 

Hi Lydia @v-yuezhe-msft & @CheenuSing

 

I got the Year to Date working, but not MTD.

 

Your help is much appreciated.

 

Thanks,

chchIntech

Moderator v-yuezhe-msft
Moderator

Re: How do I create a Month to Date and Budget total by calendar date?

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chchIntech Frequent Visitor
Frequent Visitor

Re: How do I create a Month to Date and Budget total by calendar date?

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). 

budget screen.JPGbudgetshot.JPG

Please share your thoughts and suggestions.

 

Thanks,

chchIntech

Moderator v-yuezhe-msft
Moderator

Re: How do I create a Month to Date and Budget total by calendar date?

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.