Forum Discussion

mwaters4's avatar
mwaters4
Icon for Helper I rankHelper I
6 years ago
Solved

How do I join Calendar/Date Table to Summarized General Ledger / Financial table data

I have a table that has summarized general ledger transaction data (see below example excerpt).

It is summarized at the Branch - Dept - GLAccount - PostingPeriod (Month/Year) level.

 

I have a Calendar Table I created in Power BI that has fields to account for calendar quarters as well as Fiscal Quarters, Years, etc.

My question is how should I connect this Calendar table to the GL Date table in order to be able to use time intelligence functions, etc.?

There might be some other transactional tables I bring in that have fields down to the exact date so I can do some summary/detail kinds of drill ins, but I am a bit confused as to how to handle this in Power BI to make this join work.

Can someone please help me?  Thanks in advance... 

 

Summarized GL Date Table:

 

 

Calendar Table:

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello mwaters4 

    On your GL data table add a field like [Posting Period Date] and make it the first day of the posting period in date format.  If the posting period is Sep-17 the [Posting Period Date] would be 9/1/2017.  Then you join the [Posting Period Date] into your Calendar[Date] field.

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

    Hello mwaters4 

    On your GL data table add a field like [Posting Period Date] and make it the first day of the posting period in date format.  If the posting period is Sep-17 the [Posting Period Date] would be 9/1/2017.  Then you join the [Posting Period Date] into your Calendar[Date] field.

    • mwaters4's avatar
      mwaters4
      Icon for Helper I rankHelper I
      6 years ago

      Thats great confirmation!  I did that shortly after posting this, but wasnt sure if that would be sufficient or not... 

      Thanks!

       

      Now... to tweak my question a bit... I was next trying to use time intelligence functions to get Rolling 12 month averages, YTD numbers of different accounts, departments, etc. and other measures.

      Do you know if that is possible given the join method you are mentioning above (and that I have used)?

       

      Thanks again... 

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        6 years ago

        Yep, it is.

        We want a measure that just sums the amount from the GL table.

        Total Amount = SUM ( 'YourTable'[Amount] )

        Then YTD amount is like this

        Amount YTD = 
CALCULATE(
    [Total Amount],
    DATESYTD(Dates[Date] )
)

        And rolling 12 is like this

        Sales R12 = 
VAR MaxDate = LASTDATE( Dates[Date] )
RETURN
CALCULATE(
    [Total Amount],
    DATESINPERIOD(Dates[Date],MaxDate,-12,MONTH)
)

        If you are concerned with the amount showing on dates that have not happened yet (both YTD and Rolling 12 will project past today) take a look at this article describing how to stop the calc from passing today.

        https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/