Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Eric Rosen]

I have a general ledger history table with:  GL Account Number;  Month in Year (eg Jan 2020); Fiscal Year, Location and Amount.   I can create measures using all the standard functions–sum, sumx, etc on the Amount and the measures work.   However, when I link to a Date table to perform time calculations (Feb 2020 vs Feb 2019 totals by location, for example) I CANNOT get the time calcs to work.    
I’ve followed various online videos and learnings, using both SAMEPERIODLASTYEAR and DATEADD functions–nothing ‘moves’ the date to show the prior period’s amounts.   I have marked the Date table as a “Date Table”, joined the tables using any combination of Date and/or text fields.   
And I’ve created new columns that show each date in February (for example) with a “month column’ of Feb 1st….
At my wit’s end–can anyone suggest why joining the Date table, which has records/dates for each day, cannot link to the GL table which has monthly amounts? 
Thanks in advance.

——————————
Eric Rosen
IT Manager
——————————

[Audrey Abbey]

Eric, 

Can you post some sample data in Excel?
And possibly your DAX formulas?
And example of your date table, and an example of your GL table with dummy data would be ideal. 

Your date table has a list of dates like 1/1/2020,1/2/2020, correct?
Does it have any future dates in it? (This is a sneaky thing about the build in time functions like SAMEPERIODLASTYEAR)
Does your GL table also have a date column in the same format (1/1/2020, 2/1/2020)? 
Do you have a relationship created between GL And Dates? Which columns did you connect on?
Do either of the date columns have a time portion? 

And can you expand on what results you are getting?
Is it showing a blank?
Is it showing a value that makes no sense?
Is it showing a value that you know is incorrect?
Are you getting an error?

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

[Christopher Schnaars]

Hi, Eric:

I’ll just throw this out there because it drove me crazy the first time I encountered it: Is it possible the dates in your date table are dates but the dates in your ledger table are datetime? If so, you’ll need to convertthe datetime field to a date. If you need to preserve the time values in your ledger, make a copy of the datetime field, set that column’s type to date and join that field to your date table.

——————————
Christopher Schnaars
Laboratory Testing Inc.
Hatfield, Pennsylvania
——————————
——————————————-

[Eric Rosen]

Chris: 
Good idea…tried that a few weeks ago, with no luck.

——————————
Eric Rosen
IT Manager
——————————
——————————————-

[Eric Rosen]

Audrey, 
Sample PBIX attached.  
The DAX is straightforward:     Total Amount=Sumx(GLTable[Amount])
                     and then Total Amount Last Year = CALCULATE ([Total Amount],sameperiodlastyear(DateTable[Date])    
                     or  Total Amount Last Year = CALCULATE ([Total Amount],dateadd(DateTable[Date],-1,Year)    

Neither formula works.   totally baffled.

——————————
Eric Rosen
IT Manager
——————————
——————————————-

[Audrey Abbey]

Hi Eric, 

Thanks for the sample. 

The issue is that your date table does not have continuous dates in it. 
All of the native time intelligence requires a date table with no gaps. 

You either need to handle your date intelligence manually, or you need to fill in the gaps on your time dimension. 

Here is Reza’s write up that I use to create date tables when I don’t have a data warehouse to connect to.
But you can also just create a list of dates in Excel and connect to it as a data source. 
https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query

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

[Dan Edwards]

Eric,

Here you go.  I made the adjustments in your file.  There were a couple issues.  

1. The relationship between GL table and Date table.  The fields were not the same data.  MM/YY to MM/DD/YY.
2. To fix this, I added a column to the GL table to add a full date MM/DD/YY (I just set everything to the first of the month)
3. On your reports, you should be using the year column from your date dimension table and not your GL fact table.

I have attached the adjustments so you can see the changes I made.

——————————
Dan Edwards
Senior Manager
Crowe LLP
Huntington Beach CA
714-231-2202
dan.edwards@crowe.com
——————————
——————————————-

[Eric Rosen]

Dan,
Thank you for your help.  The date relationships (MM/YY vs MM/DD/YY) have been confusing, to say the least.  Appreciate your time. 
Eric

——————————
Eric Rosen
IT Manager
——————————
——————————————-