Power BI - 2 fact tables to use same month end date

It’s more important to learn a repeatable process to solve such problems than for someone to give you the answer. Here is my advice

Create a table visual and put months (say mmm-yyyy) or similar in the visual on rows (sourced from the calendar table).
Write separate raw measures for each fact table sum(value) and stick in the visual.

Once you do this you can “see” the problem.
Now write a measure that combines both into 1. Is the answer right? If not, how would you fix it in Excel? Maybe with an IF statement or similar. Probably same/similar applies in DAX.

You need to work out when to stop using one and when to start using the other. It depends on your data.

Does that help?

Matt Allington said:

It’s more important to learn a repeatable process to solve such problems than for someone to give you the answer. Here is my advice

Create a table visual and put months (say mmm-yyyy) or similar in the visual on rows (sourced from the calendar table).
Write separate raw measures for each fact table sum(value) and stick in the visual.

Once you do this you can “see” the problem.
Now write a measure that combines both into 1. Is the answer right? If not, how would you fix it in Excel? Maybe with an IF statement or similar. Probably same/similar applies in DAX.

You need to work out when to stop using one and when to start using the other. It depends on your data.

Does that help?

Click to expand...

Thank you. I want to learn but also need to have this done fairly soon. I have been trying to correct for over a week so at this point I am running out of ideas. I can find YouTube videos that fit what I am trying to do. I am pretty new to Power BI so there has been a learning curve. Thanks.

Follow my steps and post what you find back here

Matt Allington said:

Follow my steps and post what you find back here

Click to expand...

I made an attempt I am getting the wrong data back or errors. Thank you.

OK. How do you think I can help you from here given the information you have provided?

Matt Allington said:

OK. How do you think I can help you from here given the information you have provided?

Click to expand...

Here is my tables. 2 fact tables "Cost Center_2024_Data" & "LY_Bud_LTF_Data". I have been able to write measures for MTD, and YTD from the Cost Center_2024_Data table which stores the 2024 actuals. I have a table the has the max date from the Cost center_2024_Data that I am trying to use as the month end date for each month end close. So this month end I would have 2024 data through May 31, 2024. I am trying to get last year and budget data from the LY_Bud_LTF_data table to have the same date. So my budget column would have the May 2024 budget number. And another column for YTD budget and full year budget column. Then the same for last year. May 2023 total, 2023 YTD for we can compare current month, YTD and full year data. I have tried a number of different measures to get the LY and Budget data but I get either blank data or funky numbers that don't tie to the spreadsheet. Hopefully this helps show what i am trying to do. Thank you once again for your time.

Your 2 tables look basically the same, (possibly except for the version column). I would put everything in the one data table and call it data, for simplicity sake. Then write a measure for actual and budget and make sure they work. To see monthly data, use a month end date or mmm-yyyy column in the calendar table and test against your 2 measures. Make sure they work. Then the MTD and YTD should be easy using TOTALYTD and TOTALMTD.

I’m not sure what logic you want to use for the current month. May is not finished hence you will have a part month this year but the layout described above will show a full month’s data for last year. One way to do it is add a day of year column in your calendar table. You can use LOOKUPVALUE and TODAY() to fetch the day of year for today. You can also use MAX() of your data table to find the last date loaded this year. Once you have that, you can add a filter on the day of year column to be <= the day of year number this year to filter out the extra days of the month for last year.