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

chadwood3232

New Member
Joined
Nov 22, 2011
Messages
20
Hello. I have two fact tables in Power Bi. One has 2024 cost data through April. The other has 2023 cost date for all of 2023 as well as budget for all of 2024. I can get current month, previous moth, and year to date to calculate correctly with the 2024 data. But I get the full year data for the 2024 budget as well as as 2023 since all of the months in the data. I am trying to use the max date from table 1 to give me the correct summary for 2023 as well as 24 data. Can anyone help me figure out DAX to help me get month end april for 2023 and 2024 budget?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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?
 
Upvote 0
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?


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.
 
Upvote 0
OK. How do you think I can help you from here given the information you have provided?
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.
 

Attachments

  • powerbi.jpg
    powerbi.jpg
    73.9 KB · Views: 7
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top