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 Cal...
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.

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/