Forum Discussion

thethakuri's avatar
thethakuri
Frequent Visitor
5 years ago

Date dimension table not working with Generate(d) table

I have following tables:

  • sales_target (from DB)
  • wip_forecast (from DB)
  • DateDim (date dimension table)
  • Cashflow Forecaster (created from another table using "Generate") 

 

 

All dates are of Date type and mmmmYYYY format:

 

My issue is, when I try to sort the data using DateDim[Date MMYY], it works with columns from sales_target & wip_forecast tables but not Cashflow Forecaster. As you see in below, I just get the sum of a column from Cashflow Forecaster as a point and not spread over like other fields.

 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Assuming you are expecting to see dates in the future (hence the Forecast), do you have future dates in your date table? And what are the relationship fields from Date Table to forecast table? Finally, can we see the formula you are using?

    Thanks!-- Nate

  • thethakuri's avatar
    thethakuri
    Frequent Visitor
    5 years ago

    Hi Nate,

     

    My dates table have dates ranging from April 2008 to 10 years in future (June 2022), more than enough for forecast table.

    FILTER (
    CALENDAR ( MIN ( 'invoice'[date_issued] ), DATE(YEAR(NOW())+10,MONTH(NOW()), DAY(NOW()))),
    [Date] = EOMONTH ( [Date], -1 )+1
)

    Here's the relationship fields from Date Table to forecast table:

    Forecast table:

    CashflowForecast Table = GENERATE ( 'FilteredQuote Table', GENERATESERIES ( 1, 'FilteredQuote Table'[forecast_months], 1 ))
    FilteredQuote Table = CALCULATETABLE('quote',FILTER('quote','quote'[stage]<>"Archived"&&'quote'[stage]<>"Deleted"))

     

     

     

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    Hi Nate,

     

    My dates table have dates ranging from April 2008 to 10 years in future (June 2022), more than enough for forecast table.

    FILTER (
    CALENDAR ( MIN ( 'invoice'[date_issued] ), DATE(YEAR(NOW())+10,MONTH(NOW()), DAY(NOW()))),
    [Date] = EOMONTH ( [Date], -1 )+1
)

    Here's the relationship fields from Date Table to forecast table:

    thethakuri_0-1624834834393.png

    Forecast table:

    CashflowForecast Table = GENERATE ( 'FilteredQuote Table', GENERATESERIES ( 1, 'FilteredQuote Table'[forecast_months], 1 ))
    FilteredQuote Table = CALCULATETABLE('quote',FILTER('quote','quote'[stage]<>"Archived"&&'quote'[stage]<>"Deleted"))