Hi,
I have a table in which data is not entered everyday. And I have a date table as well that runs from min to max of that data table for each account there is. What would be the solution to add the missing records for the dates that are not entered in the data table? The data should be for each account for each day. The values should be zero and the cumulative totals should still be calculated. The account,value,cost,cost2,cost3 should be zero when the missed records are added but the running total should still be calculated according to the dates and account. MissedDatesRecords.pbix (82.2 KB) ReqResult.xlsx (8.7 KB)
Creating a virtural table using variables is one thing I am trying to do, but with the dates increasing, the model size also increases with all the other columns that are there and it is like we are creating another table. Is there a way to implement this? Appreciate your inputs both in dax and power query.
Have a Date table and create relationship with Date table and transaction table
Create measures :
Total Value = calculate(sum(Data[Value])) : will return the values for the transaction days only… we may not get values for the missing days
Total Value(including missing days = COALESCE([Total Value],[Total Value],0) OR
COALESCE(SUM(Data[Value]), 0)
This will give you zero value for the missing days
Hello @Vsb79, just following up if the response from @Umasuresh help you solve your inquiry?
We’ve noticed that no response has been received from you since a few days ago. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Thank you, Uma for the looking into it and responding to my request.
But, my problem statement is such that if I have a date filter, the sum total values should not change when I change the starting of the date filter and only change when the end date of the date filter is changed.
I figured the solution to my problem statement is to create a table with the cross join between date table and the data table and use summarizecolumns to create a new table that has all the columns I need to do the calculations.
Hi @Vsb79, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.
