Forum Discussion
Calculate MTD Values from two disconnected Tables.
Hi Everyone,
I have a budget table, which is having total budget of the month on last day of month.
Like-
and another table is Date Table, and I want to allocate same value for all date of the month like MTD.
I tried with TREATAS Dax funtion but result is not what I needed,
M_BUDGET_MTD_SALES_Test =
VAR CurrentDate = MAX(DIM_DATE[DATE])
VAR Month_start = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)
VAR Month_end = EOMONTH(CurrentDate, 0)
VAR TotalMonthBudget =
TOTALMTD(CALCULATE(
SUM(BUDGET_FORECAST[BUDGET]),
TREATAS(
VALUES(DIM_DATE[DATE]),
BUDGET_FORECAST[MONTH]
)
), DIM_DATE[DATE])
RETURN
TotalMonthBudget
Result -
I want the same total for each date.
Thanks in advance.
Deepak
Hi DeepakSharma_01 ,
In the solution I originally provided, there was no relationship between the date table and the budget table.
To reproduce your problem I created a relationship between the two tables.
The relationship between two tables can be ignored by using the REMOVEFILTERS function.
The modified syntax is as follows:
M_BUDGET_MTD_SALES_Test = VAR CurrentDate = MAX(DIM_DATE[DATE]) VAR Month_start = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1) VAR Month_end = EOMONTH(CurrentDate, 0) VAR TotalMonthBudget = CALCULATE( SUM(BUDGET_FORECAST[BUDGET]), REMOVEFILTERS(DIM_DATE), BUDGET_FORECAST[MONTH] = EOMONTH(CurrentDate, 0) ) RETURN IF( CurrentDate >= Month_start && CurrentDate <= Month_end, TotalMonthBudget, BLANK() )The final page visualization is shown below:
If it hasn't been resolved, please provide a Power BI Desktop file in progress (with sensitive information removed) that fully covers your issue or question in a usable format (not a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive (set up public access), SharePoint, or a Github repository, and then share the URL of the file.
The pbix file is attached.
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf 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!
5 Replies
Hi DeepakSharma_01 ,
Your solution is great, BeaBF . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.
Your requirement is to calculate the budgeted values assigned on a monthly basis (MTD) and assign the same values for each day in the date table, right?
Here is my test data:
MONTH
BUDGET
2025-01-31
3000
2025-02-28
2000
2025-03-31
1000
2025-04-30
5000
I created a date table:
Modify your measure as follows.
M_BUDGET_MTD_SALES_Test = VAR CurrentDate = MAX(DIM_DATE[DATE]) VAR Month_start = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1) VAR Month_end = EOMONTH(CurrentDate, 0) VAR TotalMonthBudget = CALCULATE( SUM(BUDGET_FORECAST[BUDGET]), FILTER( BUDGET_FORECAST, BUDGET_FORECAST[MONTH] = EOMONTH(CurrentDate, 0) ) ) RETURN IF( CurrentDate >= Month_start && CurrentDate <= Month_end, TotalMonthBudget, BLANK() )The final page visualization is shown below:
The pbix file is attached.
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf 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!Hi Anonymous & BeaBF ,
I tried solution of yours but getting random values in cell but total is correct, It is happing because of having indirect relationship between date table & budget table. Is there any way to temporarily ignore existing relationship.Hi DeepakSharma_01 ,
In the solution I originally provided, there was no relationship between the date table and the budget table.
To reproduce your problem I created a relationship between the two tables.
The relationship between two tables can be ignored by using the REMOVEFILTERS function.
The modified syntax is as follows:
M_BUDGET_MTD_SALES_Test = VAR CurrentDate = MAX(DIM_DATE[DATE]) VAR Month_start = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1) VAR Month_end = EOMONTH(CurrentDate, 0) VAR TotalMonthBudget = CALCULATE( SUM(BUDGET_FORECAST[BUDGET]), REMOVEFILTERS(DIM_DATE), BUDGET_FORECAST[MONTH] = EOMONTH(CurrentDate, 0) ) RETURN IF( CurrentDate >= Month_start && CurrentDate <= Month_end, TotalMonthBudget, BLANK() )The final page visualization is shown below:
If it hasn't been resolved, please provide a Power BI Desktop file in progress (with sensitive information removed) that fully covers your issue or question in a usable format (not a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive (set up public access), SharePoint, or a Github repository, and then share the URL of the file.
The pbix file is attached.
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf 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!
- BeaBF
Super User
DeepakSharma_01 Hi!
You need a measure that assigns the same total monthly budget to each day of that month. You can achieve this with the following approach:
M_BUDGET_MTD_SALES_Test =
VAR CurrentDate = MAX(DIM_DATE[DATE])
VAR MonthStart = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)
VAR MonthEnd = EOMONTH(CurrentDate, 0)VAR TotalMonthBudget =
CALCULATE(
SUM(BUDGET_FORECAST[BUDGET]),
BUDGET_FORECAST[MONTH] = MonthEnd
)RETURN
TotalMonthBudgetBBF
