Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
3 years ago

Date Tables and using multiple dates in data source

Hi all

 

I have a data source which has two date columns (Activity Date & Request Date).  I only use Activity date when calculating Activity, and only need to use Request date, when calculating Referrals.  Problem is, my date table bundles them all up and can't diffrerentiate between what is the activity date, and what is the request date...!?

 

I've been trying to develop two measures which count a rolling 12 month period, then a rolling 12 month BEFORE the 12 month period (measures as follows):

Rolling 12 = CALCULATE(count('Activity & Demand'[AD_Event_Key]), 'Activity & Demand - TEST FILES FOR TEST APP '[AD_Data]="Activity",DATESINPERIOD('Activity & Demand - TEST FILES FOR TEST APP '[AD_Event_DT],ENDOFMONTH('Activity & Demand - TEST FILES FOR TEST APP'[AD_Event_DT]),-12,MONTH))  
 
Rolling 12 before 12 = CALCULATE(count('Activity & Demand - TEST FILES FOR TEST APP'[AD_Event_Key]), 'Activity & Demand - TEST FILES FOR TEST APP '[AD_Data]="Activity", DATESINPERIOD('Activity & Demand - TEST FILES FOR TEST APP'[AD_Event_DT],ENDOFMONTH(DATEADD('Activity & Demand - TEST FILES FOR TEST APP'[AD_Event_DT]-12,MONTH)),-12,MONTH))

 

Problem is, the second formula will display correctly when I have nothing selected, but when I select say an exam type, the measure can't display and says something about needing to use contiguous dates...I suspect strongly I need to sort something in the date table but am at a loss!

 

I can drop in a link if it helps?

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Creative_tree88 ,

     


    //I can drop in a link if it helps?

    It would be helpful, please erase sensitive data, thanks.

    In order to use any time intelligence calculation, you need a well-formed date table. The Date table must satisfy the following requirements:

    • All dates need to be present for the years required. The Date table must always start on January 1 and end on December 31, including all the days in this range. If the report only references fiscal years, then the date table must include all the dates from the first to the last day of a fiscal year. For example, if the fiscal year 2008 starts on July 1, 2007, then the Date table must include all the days from July 1, 2007 to June 30, 2008.
    • There needs to be a column with a DateTime or Date data type containing unique values. This column is usually called Date. Even though the Date column is often used to define relationships with other tables, this is not required. Still, the Date column must contain unique values and should be referenced by the Mark as Date Table feature. In case the column also contains a time part, no time should be used – for example, the time should always be 12:00 am.
    • The Date table must be marked as a date table in the model, in case the relationship between the Date table and any other table is not based on the Date.

     

    Best Regards,
    Gao
    Community Support Team

     

    If 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!

    How to get your questions answered quickly -- How to provide sample data