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

[chchIntech]

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.

[CheenuSing]

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

[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

[chchIntech]

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.

[v-yuezhe-msft]

Hi @chchIntech,

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

Thanks,
Lydia Zhang

[chchIntech]

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]

Hi Lydia @v-yuezhe-msft & @CheenuSing

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

Your help is much appreciated.

Thanks,

chchIntech

[v-yuezhe-msft]

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

[chchIntech]

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

[v-yuezhe-msft]

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

[chchIntech]

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

[v-yuezhe-msft]

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

[v-yuezhe-msft]

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

[drodrigues]

Hi Lydia @v-yuezhe-msft,

Private messaged you the PBIX file shared link.

Thanks,

chchIntech