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

[chadwood3232]

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?

 

[Matt Allington]

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?

 

[chadwood3232]

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.

 

[Matt Allington]

Follow my steps and post what you find back here

 

[chadwood3232]

Follow my steps and post what you find back here

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

 

[Matt Allington]

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

 

[chadwood3232]

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.

 

[Matt Allington]

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.