Help with monthly income measure with Power BI Direct Query

[FD5]

Hi,

I have several datasets which I am direct quering to, to be able to show data from all tables in one report.

I am struggling to show period and year to date income figures. If I bring in the month, YTD doesn't work and visaversa.

Can I create a new measure where it shows the monthly income figure or YTD, I don't mind whicever as long as it works.

My table names are

Bills

BaseCalendar

Currently I selected these fields and obvs I am only getting the monthly figures. I can't work out how to do both, monthly and YTD.

Any help will be much appreciated.

Many thanks

Figen

[v-huijiey-msft · Accepted solution]

Hi @FD5 ,

Thanks for the reply from @DataNinja777 .

As I understand it, your requirement is to calculate monthly totals and year-to-date totals, correct?

Please try:

Here is the example data I created:

Date	Sales
2023-12-20	1000
2023-12-31	1000
2024-01-01	100
2024-01-05	200
2024-02-01	300
2024-02-05	200
2024-03-01	4
2024-03-05	500
2024-04-01	80
2024-04-05	120
2024-05-01	450
2024-05-05	120

Create a calculated column that calculates the current month:

Month = MONTH('Table'[Date])

Create a measure that calculates the monthly totals:

MonthSales = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Month]))

Create a measure that calculates the year-to-date total:

SalesYTD = TOTALYTD(SUM('Table'[Sales]), 'Table'[Date]. [Date])

The page effect is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[DataNinja777]

Hi @FD5 ,

For your YTD calculation to work, you need to create a separate calendar table in your data model and link the date key in your calendar table with the date key in your fact table.  It appears that you are not taking advantage of the time intelligence function which dax offers.  Your visual appears to utilize the "Period month name" from your fact table instead of fields from your calendar table.   The first step for an efficient data analysis is building a proper data model with star schema with your fact tables and dimension tables.   

Best regards,