date table not working properly

[monojchakrab]

Hello good people of PBI community,

Recently I have run into a problem while changing the fact table due some updation that I required. I ran some M code to split the date column into date and time and hence am working off this fact table now (let us call it the new_table)

The date table that I had generated (code as in the screengrab) was basis the earlier fact table (let us call it old_table)  - I have relinked the date table to the new_table. So the date table has no relationship with the old table.

However when I am trying to make a chart with dates and a day running total - the chart is buffering interminably without generating anything - it works when I changed the dax formula to include dates from the purchase table and not the date table

Day Running Total = 
VAR Datelast = MAX('Amazon YTD Apr22_new'[Purchase Date])
VAR Result =
    CALCULATE([Total Sales],'Amazon YTD Apr22_new'[Purchase Date]<=Datelast)
RETURN Result

The date table instead of auto-generating dates as per the date column in the fact table (new_table) and now given dates from 01/01/1899! and that I think is the reason for interminable buffering.

Can anyone help me with this please? If the fact table changes and there is a new relationship between the date table and the date column of the fact table - will the calendarauto function auto-update with the new dates in the new date column? I think that is not happening here.

Thanks and really appreciate your time.date table formula, dates from date table & dates from fact table

The date from date table works fine in other visual as shown below :

mtd sales with dates from the date table

Please note the starting date in the two screen shots - in the first the starting year is 1899, whereas in the second one, it matches with the exact purchase dates from the fact table.

[monojchakrab · Accepted solution]

Hi @amitchandak ,

I tried the code as suggested as below :

Day Running Total = 
VAR Datelast = MAX('Amazon YTD Apr22_new'[Purchase Date])
VAR Result =
    CALCULATE([Total Sales], FILTER(ALL('Date Table'),'Date Table'[Date]<=Datelast))
RETURN 
    Result

But unfortunately it is still buffering...as the screengrab shows :

buffering

I think it is definitely because the date column in the date table, which somehow is starting from 1899 and that is why the the cumulative day total is taking so much of time.

[mahenkj2 · Accepted solution]

Hi,

Excuse me for intervening by not offering direct solution, but perhaps simpler approach for handling date table. I usually create a date table starting from the year of interest to latest date. Then relate date field of this table with date fields of fact tables. Certainly since this is a calculated table, will increase the file size, but I dont think that is a concern at all, considering usability, simplicity etc.

Dates = CALENDAR (DATE (2019, 1, 1), DATE (YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())))

This does not depend on fact table and can be used multiple times in the data model. 

[monojchakrab · Accepted solution]

Hi @amitchandak ,

I tried the code as suggested as below :

Day Running Total = 
VAR Datelast = MAX('Amazon YTD Apr22_new'[Purchase Date])
VAR Result =
    CALCULATE([Total Sales], FILTER(ALL('Date Table'),'Date Table'[Date]<=Datelast))
RETURN 
    Result

But unfortunately it is still buffering...as the screengrab shows :

buffering

I think it is definitely because the date column in the date table, which somehow is starting from 1899 and that is why the the cumulative day total is taking so much of time.

[monojchakrab]

@amitchandak - This also works. I ran it a few time. Except that it takes a while longer as I dont have a very fast machine to process all these dates quickly enough

[amitchandak]

@monojchakrab , if you have date table joined with date of you fact, you need have measure like below and use date table's column in visual

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

[monojchakrab]

I will try this @amitchandak . But I think the filter (or removal of filter is an issue).

I did some kind of a jury-rig and used the calendar function instead of calendarauto and now it seems to be working.

Date Table = 
ADDCOLUMNS(
CALENDAR(date(2022,1,1),date(2022,12,31)),
        "YEAR", YEAR([Date]),
        "MONTH NAME",FORMAT([Date],"MMM"),
        "MONTH NUMBER",MONTH([Date]),
        "DAY NAME",FORMAT([Date],"DDD"),
        "WEEK NUMBER",WEEKDAY([Date],2)
)

But not sure if that is the right way as the date column in the fact table will elongate as more dates will be added as new data is added to the table and the model is refreshed.

Calendarauto seems to be a better option and I will see if your solution works if I use the calendarauto function to generate the date table.

Thanks and really appreciate the quick response.