Mapping Issue with Event Table and Calendar Table

[aakif_aslam]

Hello to all,

In this case - Event table has the data where dates are not in a continous manner and Calendar table is the date range of Min/Max of Event_Date column. I'm unable to establish a working relationship with date column between Event and Calendar table.

 After mapping the calendar icon changes of Event_Date

SAMEPERIODLASTYEAR function does not show last year values if I used the Event_Date from the Event table, also after mapping with calendar table when the dates are selected from calendar table there is no data displayed at all. 

I'm unable to understand the issue.

Please HELP.

@amitchandak 

@Greg_Deckler 

@Shaurya 

@lbendlin 

@ImkeF 

[menglaro78]

Hello @aakif_aslam ,
you have two solutions.

SOLUTION A - WITHOUT TABLE DATE.

In Power BI, Option And Settings, in Current File|Data Load, setting ON option Time Intelligence.
In this case you can use SAMEPERIODLASTYEAR with Event Data.

SOLUTION B - WITH TABLE DATE.

Select Table Date and in Table Tools use option "Mark as Date Table".

To do this remeber this rules: 

When you specify your own date table, Power BI Desktop performs the following validations of that column and its data, to ensure that the data:

• contains unique values
• contains no null values
• contains contiguous date values (from beginning to end)

In this case, use Date from Date Table in SAMEPERIODLASTYEAR .  

Documentation here: Set and use date tables in Power BI Desktop - Power BI | Microsoft Learn

Marco

[aakif_aslam]

Thanks @menglaro78 for the reply.

From what I understand, for Time Intelligence to work date must be continous which is not the case in Events Table. Hence Solution A won't work.

I tried Solution B but it didn't work.

Mapping one-to-many is visible but when I select any month from Calendar Date in slicer, shows blank fields from Events Table. 

[menglaro78]

Hi, is it possible to share your file?

This is not correct: Hence Solution A won't work..

In solution A , Power BI generate automatically generates a hidden calendar table with contiguous dates.

Marco

[aakif_aslam]

@menglaro78, it is a client sensitive data. I'm not allowed to share it.

Anyways thanks for the help, I'll need to check some workaround for this.