Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Date link to Calendar

  • 1.  Date link to Calendar

    Posted Nov 08, 2019 07:05 AM
    Hi Everyone.
    I have a simple sales table in Access which I connect to in Power BI. The only date fields available are Fiscal Year (format: 'F17') and Fiscal Mth (format: '7').
    As part of the query to connect to the sales table, I use merge query to bring in the Calendar mth (eg '7') and Calendar Year (eg. '2019') [from a calendar table]  based on the fiscal mth and year available in Sales. I then create a notional 'date' field based on the first day of the month using calendar mth & calendar year brought in from the merge. I then link this new 'date' field to my calendar table.
    My connection from Power BI is straight into the Access db, and there are approx 150k rows. As part of my query, I also filter to exclude data from older fiscal years (so only bringing in F18/F19 data etc).
    Unfortunately this results in a very slow query refresh and wonder if it is how my date addition is working.
    Is there a better way to do this please?

    Many Thanks
    AW​

    ------------------------------
    Many Thanks
    AW
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Date link to Calendar

    Top Contributor
    Posted Nov 08, 2019 09:07 AM
    Hi Andy,

    Hard to say if that is the cause, but it is possible.
    Access can also just be slow.
    However, you are going in a circle by merging with the calendar table and then linking back to calendar again.
    So that may very well be the issue.

    You don't really need the join back to the calendar table.
    You can create the date from the columns in the main table.

    In the query editor:
    Duplicate the Fiscal year column. Run Replace transform and replace F with 20. Set to integer data type.
    Duplicate the Fiscal month column. Run Replace transform and replace F with nothing. Set to integer data type.
    Add a custom column using the following Power Query formula: = #DATE([YearColumn], [MonthColumn], 1). Set to date data type.
    Create a relationship between the column and your Calendar table in the relationship panel.

    Let me know if that helps.
    If not, we can look at other options.


    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Date link to Calendar

    Posted Nov 08, 2019 12:29 PM
    Hi Audrey - thanks for the advice. The only problem I see is that within the sales table, it uses Fiscal Yr & Month and I need to be able to reference the Calendar table, If I created a date using fiscal that would not be strictly correct as fiscal month 1 is actually July - does this make sense?
    Hence why I had to reference the calendar table first to bring in the corresponding calendar month to generate a date.
    Kind Rgds
    Andy

    ------------------------------
    Andy Westerman
    441723587038
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Date link to Calendar

    Top Contributor
    Posted Nov 08, 2019 12:33 PM
    What is your fiscal month offset?
    You can use that to correct the fiscal month. It is just an integer, so you can do math.

    Example:
    If your fiscal month 1 is July:
    Fiscal 1 + 6 = Calendar July
    Fiscal 2 + 6 = Calendar August
    ...

    Will that help?


    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Date link to Calendar

    Posted Nov 08, 2019 12:41 PM
    That may work - I simply need to add one into my calendar.

    Thanks, I never thought of that workaround.

    Andy

    ------------------------------
    Andy Westerman
    441723587038
    ------------------------------

    Conference-PBI_200x200